texts: SSMS 2016 CTP3.1 November Preview (v13.0.800.111) bug? properties error: missing column federated_service_account

texts:
Using SSMS 2016 CTP3.1 November Preview (v13.0.800.111) on my laptop, whenever I right click any database object (the database, a table, etc.) I get the below error.

  TITLE: Microsoft SQL Server Management Studio

  Cannot show requested dialog.

  ADDITIONAL INFORMATION:

  Cannot show requested dialog. (SqlMgmt)

  Attempt to retrieve data for object failed for Server ''.  >(Microsoft.SqlServer.Smo)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.800.111+((SQL_Main).151121-1556)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Invalid column name 'federated_service_account'.
  Invalid column name 'federated_service_account'. (Microsoft SQL Server, Error: 207)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.0700&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

  BUTTONS:

  OK

I was able to run a Profiler trace to determine the query that's being run to throw this error:

SELECT
eds.location AS [RemoteDataArchiveEndpoint],
eds.name AS [RemoteDataArchiveLinkedServer],
rdad.remote_database_name AS [RemoteDatabaseName],
rdad.federated_service_account AS [RemoteDataArchiveUseFederatedServiceAccount],
case when rdad.federated_service_account = 1 then null else cred.name end AS [RemoteDataArchiveCredential]
FROM
sys.remote_data_archive_databases rdad
INNER JOIN sys.external_data_sources eds ON rdad.data_source_id = eds.data_source_id
LEFT OUTER JOIN sys.database_scoped_credentials cred ON eds.credential_id = cred.credential_id

Anyone recognize this query? Where is it coming from, what's the source? Anyone else experiencing a similar error? Is this an SSMS bug?

Some interesting repro notes:

This error happens while connecting from my laptop to the database that's on a VM in my organization.
While RDP'd in to the VM and running SSMS locally on the VM and connecting to localhost, the error does not repro.
The SSMS version on the VM (v13.0.700.242) is lower than the SSMS version on my laptop (v13.0.800.111). Hence, I'm afraid to update the SSMS on the VM to latest version as this error might throw there too.
Running the profiler trace directly on the VM and doing the repro steps does not generate the same SQL that the newer SSMS version seems to generate from my laptop.


texts: Is it possible to change the SQL Server errorlog file number on Linux?

texts:
Everything is in the title. We can change the number of errorlog files using the registry on Windows, does anybody know if it is possible on Linux? I see no mssql-conf option for this.

Thanks


texts: db2_install do not have write permission on the directory or file

texts:
This is the error I encountered (I ran the db2_install and everything else as root,):

DBI1288E  The execution of the program /home/DB_SERVER/ibm/db2/v10.5 failed. This
      program failed because you do not have write permission on the
      directory or file .

I tried to change the access permission of the path:

chmod -R a+rw /home/DB_SERVER/ibm/db2/v10.5

And then I could create a new file in that folder:

vi test

Result of ll in that directory:

total 1
-rw-rw-rw- 1 root root 12 Nov 23 00:24 test

But when I ran the db2_install again, it failed for the same reason.

PS: I tried to change permission of the setup file too

chmod -R a+rwx db2Setup/

Result of ll

total 72
drwxrwxrwx  6 root root 4096 Nov 23 00:10 db2
-rwxrwxrwx  1 root root 5349 Nov 23 00:10 db2ckupgrade
-rwxrwxrwx  1 root root 5302 Nov 23 00:10 db2_deinstall
-rwxrwxrwx  1 root root 5172 Nov 23 00:10 db2_install
-rwxrwxrwx  1 root root 5136 Nov 23 00:10 db2ls
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2prereqcheck
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2setup
drwxrwxrwx 10 root root 4096 Nov 23 00:10 ibm_im
-rwxrwxrwx  1 root root 5190 Nov 23 00:10 installFixPack
drwxrwxrwx  4 root root 4096 Nov 23 00:10 nlpack
-rw-r--r--  1 root root    8 Nov 23 01:00 test

So I have no idea what's wrong? How to fix this?


texts: EXECUTE denied, but only when I'm connected locally

texts:
I created a stored procedure on a MSSQL server from my local machine via SSMS.  I see this SP in the object explorer when I expand the database 'Stored Procedures' node.  I am able to execute the SP in the normal manner.

However, if I RDP to the SQL Server, load SSMS on the server, and expand the 'Stored Procedures', I don't see the SP.  In fact, there are several stored procedures I don't see.  If I try to execute the stored procedure, I get an error message:

Msg 229, Level 14, State 5, Procedure SayHi, Line 1
The EXECUTE permission was denied on the object 'SayHi', database 'master', schema 'dbo'.

I have verified:

I am connecting to the correct server via RDP
I am connecting to the same server in both SSMS sessions
I am viewing the same database (master) in both SSMS sessions
I am logging in as the same user in both SSMS sessions

Also:

This is a procedure that I just created.  Even when executing on the server, the server knows the procedure exists - otherwise the error message would say "Could not find stored procedure".  That's why I'm confident I'm communicating with the correct server / database.  The other SP's that I'm unable to see have been around for a long time - some I created, others were already there.
I have 'Refresh'ed the tree (irrelevant to the EXECUTE denied response)
The SP is in the master database, if that makes any difference
My local SSMS is SSMS 2017, the server is SQL 2012 SP3
My colleague sees all of the SPs regardless of whether he is using SSMS on his own machine or while RDP into the server.
I have tried logging out of the server and restarting SSMS.

Any ideas why I can't see / execute the SP while logged in at the server, despite being logged in as the same user??


texts: MySQL with symlinked databases on an external drive on MacOS cannot read idb files

texts:
I have had this setup working for years, but all of the sudden today, MySQL crashed, and now I'm having trouble with every database on my external drive on my Mac.
It is currently running MySQL 8.0.21, and that update was done 3 months ago. Prior to that is when I say "working for years" since verstion 5.6/5.7. I haven't updated the OS recently either -- it has been running Catalina for a while.
Initially after the crash, the databases could not be recovered automatically by InnoDB. When it started up, it had lines for all of the tables in every one of those DBs that mentioned they could not be recovered because they were “in an unprotected location.”
Eventually, I figured out how to change the permissions for the users on the external drive to match those expected by mysql, and put this extra location in innodb_directories config setting, and it starts up OK.
However, now, when trying to select data, it tells me Tablespace is missing for table ... and there is a new error in the error log:
Operating system error number 1 in a file operation.
Error number 1 means 'Operation not permitted'
Cannot open datafile for read-only: ... OS error: 101

Not sure why it’s giving me both error 1 and 101, but according to perror, 1 is indeed operation not permitted, and 101 is STREAM ioctl timeout.
As for “not permitted” — the permissions were actually more lenient on these folders. drwxr-xr-x vs. drwxr-x---  (so, world readable and executable) and the ibd files within them are similarly more lenient: -rw-r--r-- vs -rw-r-----.
I don’t even know where to start with error 101, except to try to clear error 1 first, and see if it goes away. The drive is absolutely accessible, even by mysql, which could read it to start up, as best I can tell (since the errors regarding them being unprotected went away).
I decided to chmod o-r and chmod o-x to make those folders and tables match exactly the permissions on the ones that worked. I restarted mysql to see if that made a difference in its ability to read those tables in the databases on the external drive, but it gives the same errors.
I moved a database folder that was working from the main drive to the external drive. It continued to work, until I restarted MySQL. Then it started up as normal, but when selecting data again it gave the same error as before. I moved a database from where it didn't work on the external drive, to the main MySQL folder, and restarted MySQL, and it works fine. Move it back, and we get the same errors.
MySQL runs under the same user account that I am logged in as, so I don't see why it would have an issue with any kind of permissions related to the user account.
Finally, I tried adding mysqld and mysqld_safe to Full Disk Access in MacOS and restarting MySQL, in case MacOS was blocking it at the program level, again with no luck.
I am out of ideas. Anyone have anything else I can try?


texts: How to view the table structure in workbench in mysql database without quotes

texts:
I need to view the structure(i.e table scripts without quotes) of tabes from mysql work bench

Thanks


texts: Query from 1st domain and insert in 2nd domain

texts:
I have data in one of my websites. I want to print these data in other site. These two are in same server. At present, I have to export MySQL data and import into other then do this code to insert in 2nd domain.

I want to link directly so that I can make data request from 1st website and show in 2nd website.

I know for this to happen, I need to enable bind-address = 127.0.0.1 to ip address of server and I have changed this.

After that in first website, I have granted permission for that table using this command:

GRANT select ON `database-name.tableA`.* TO 'userB'@'localhost';

but I am getting this error:

  Failed to connect to MySQL:
  Access denied for user 'userA'@'localhost' to database 'database-name.tableA'

The code I am running from website B:

databaseconnect.php

    <?php
    $con=mysqli_connect("localhost","UserA","passwordA","databaseA");

  if (mysqli_connect_errno($con))
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
    ?>

main.php

      <?
                    include('/home/website2/public_html/databaseconnect.php');
                    $res2 = $con->query("SELECT * FROM `website1_tableA`  where status='0' order by id asc limit 5");
                        while($result = $res2->fetch_assoc()){
                        $cont=$result['content'];
                        $tit=$result['title'];
                            echo $cont;

            }

            ?>


texts:
I'm new to mysql and GC. It may sound like opinion based question, so sorry for that. But, I really need a right opinion. My company wants to move their on-prem mysql to GC managed mysql. I was looking for feature differences and I found this link: https://cloud.google.com/sql/docs/mysql/features
However, nothing related to tablespaces mentioned here. I wanted to check if we can create tablespaces on GC for mysql and what other things can be missing there.
If anyone has exact idea about pros and cons of GC managed mysql..that will be really helpful.
Thanks in advance!


texts: Does PL/SQL support pointers, aliases, or references?

texts:
I'm writing code for an environment I also support, and I'm trying to design it to handle future changes.

The current requirement is that two fields should be identical, but I used to be a user of the software I'm supporting, and I have a hunch there may be a requirement to separate the two fields at some point.

Let's start with this example:

DECLARE
   x_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   do_thing_1(x_);
   do_thing_2(x_);
END;

If someone changes their mind on the spec, I'll have to rewrite it like the code below.

DECLARE
   x_ VARCHAR2(32767);
   y_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   y_ := 'DEF';
   do_thing_1(x_);
   do_thing_2(y_);
END;

So, I have a couple of bad options. (1) I could write the code today for the original spec I have in my hands now, or (2) I could write the code with redundant variables to handle the changes I suspect are coming.

The problem with option (1) is that I'll have to rip out what I've done later, and some of the design intent I have in my head today will be lost without a lot of verbose documentation. The problem with option (2) is that, if the changes never come, the redundant variables amount to dead code, and memory and logical reads will be wasted during run time. Option (2) would be equivalent to passing a function argument by value when a reference parameter would be more efficient. Option (2), from a developer's perspective, is partially a violation of the DRY principle, too.

-- Change never materialized. x_ and y_ are redundant.
DECLARE
   x_ VARCHAR2(32767);
   y_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   y_ := 'ABC';
   do_thing_1(x_);
   do_thing_2(y_);
END;

Does something like this exist?

DECLARE
   x_ VARCHAR2(32767);
   y_ DBMS_POINTER_THINGY.REFERENCE;
BEGIN
   x_ := 'ABC';
   y_ := DBMS_POINTER_THINGY.POINTER_TO(x_);
   do_thing_1(x_);
   do_thing_2(y_);
END;

If I use a DEFINE, I'll lose the magic when it goes server-side.

If I use conditional compilation, can I assign a value to a directive within my code without having to set it at the session level?

DECLARE
   x_ VARCHAR2(32767);
   -- something defining $$y_ here?
   $$y_ := x_
BEGIN
   x_ := 'ABC';
   do_thing_1(x_);
   do_thing_2($$y_);
END;


texts: Is there any convenience in name date-time related columns? created and delivered, … or createdTS, deliveredTS or createdAt, deliveredAt or…


texts: Running an SSIS package owned by a domain user from SQL Server running on a local service account

texts:
I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the environment looks like this:

Domain

Server 1

SQL Server running on local account
On filesystem: SSIS package
In SQL Server Agent: a job

Server 2

SQL Server running on local account

To be able to log on to both servers, I've created a domain account to be used as service account. When I use this domain account to log on to Server 1, and then execute the package from file system, every step succeeds. However, when I try to add the job to SQL Server I run into one of the following problems:

Situation 1. Job owner: local account; run SSIS step as proxy to the domain account. When I set the job owner to a local account, but run the job as a proxy to the domain account, the job itself will successfully execute, but the package throws errors like

  Execution failed with the following error: "The directory
  'LocalApplicationData' does not exist.".

This error can be fixed by creating a login with Administrator-rights for the domain user on Server 1, but this is obviously not a desirable solution. Adding the account to one of the SQL Server agent / DTS groups doesn't work either.

Situation 2. Job owner: domain account; run SSIS step as a proxy to the domain account. When I set both the job owner and the 'run as user' for the step to the domain account, the job won't start at all, with the following error:

  Unable to determine if the owner (Domain\Domain user) of job Job
  name has server access (reason: Could not obtain information about
  Windows NT group/user 'Domain\Domain user', error code 0x5. [SQLSTATE
  42000] (Error 15404)).

I believe the last error is because SQL Server runs on a local account and therfor cannot look at which rights domain accounts have.

What is the right way to make the job run? Situation 2 feels cleaner to me, but seems impossible because SQL Server runs on a local account. Situation 1 would work too, but giving a domain user administrative rights on my SQL Server is not going to happen.

UPDATE:

@JonSeigel and @Mr.Brownstone:

It seems plausible that this is issue is because of the lack of permissions. However, the error is about the non-existence 'LocalApplicationData' - one of the folders which is generated for each account. I've already logged in to the server with the credentials under which the package runs, (hereby creating a profile directory) and tried several combinations of permissions for the profile directory. Even when manually granting almost all permissions on this specific directory, I get the error mentioned above.

While doing some more research, I ran into a forum thread at http://www.sqlservercentral.com/Forums/Topic391332-148-1.aspx#bm391441 which is quite similar - without solution either though.


texts:
Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the load. I am planning to "SET GLOBAL innodb_max_dirty_pages_pct=0;" at some point before the dump. I am curious to know which combination of settings will get me the fastest dump and reload times?

SCHEMA stats:

26 myisam tables
413 innodb
~240GB of data

[--opt= --disable-keys; --extended-insert; --quick, etc]
--no-autocommit ??

vs prepending session vars like:
"SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;"

Are the mysqldump options equivalent or not really?

Thanks for your advice!


texts: Oracle schema migration to new database with zero downtime

texts:
I have a problem:
I have two production schemas on one database each serving two different appplicaitons.

I need to migrate (move) one schema to an entirely new database that will be dedicated for that application with zero downtime. SO EXPORT/IMPORT can not be used.

I have license to even active data guard. But if I setup data guard from current database DB to new DB, should I  switch over OR failover or what else so that the current primary will continue to be primary in its role and only the new DB will change its role from standby to fully operational primary. Then we simply direct the application to new and start the new db.
Thanks for your help.


texts: SSRS Reports Manager Security Configuration

texts:
I happen to be setting up a reporting server at work from scrach. Having very little almost no experience of sql server administration I am finding it really difficult to configure the security settings for the Reports Manager.
I do understand all the users who will be accessing Reports Manager will need permissions on Two Levels

1 – Permission to Access Data from sql server. (for which after some research I have decided to make use of datareader role.
2- Permission to Access Reports Manager for which I have choice to choose from Reports Manager Roles such as (Browser, Content Manager etc etc.)
For reports manager I have created a new Role with only permissions to View Reports.

Problem
With all these Security Filters in place yet when I added a Domain User Account to Reports Manager, The user can navigate anywhere on the Reports Manager (Can access folders , DataSources and reports they don’t have permissions on).
I do not want Users to view Data Sources or any other folders or Reports that they don’t have permissions on.
What I have Tried So Far

1- Added a user to One folder on the reports Manager, Yet they can navigate to any other Folder on Reports Manager.
2- Added a user to just one report , again they can navigate to any other Folder on Reports Manager.
3- I was using a virtual account for SSRS Service Account, Removed that and tried on all other 3 built in accounts for service account, LocalService, LocalNetwork & LocalSystem.  (Not sure if this would have made difference).

All these efforts in vain as , whenever a user is added to any one report or folder they can navigate to all the other folders and reports and can actually execute reports.
Users had the datareader role assigned in Sql Server and a Custom Role on Reports Manager who has permissions only to View Report.
I have set the reporting services to use the default windows authentication. It is Sql
Server 2008 R2 Reporting Services. Data Centre Edition.
Please any advice or any direction would be a great help. I have been reading BOL but nothing has helped as yet.


texts: Does AE certificate include both master and encryption keys together?

texts:
After setting up Always Encrypted, I can see a certificate in my Current User

when selecting
select * from sys.column_master_keys

under the key_path I see this location, but there is no location specified for encryption keys. So is just this certificate export enough? Does it contain both master and encryption keys together in the same certificate?
Thanks


texts: Scripting the copy of SQL Server production database to test environment

texts:
We are introducing continuous integration on our project and we decided to automate the refresh of our test database instance with fresh production data.

I'm currently looking for the best way to go in order to avoid any production disruptions or slow downs.

I first though about using backup/restore command from a command line with the sqlcmd utility...

But it seems it's problematic because it solely generates a .bak file although this file is still "linked" with .mdf/.ldf files. So when restoring it in our test environment these .mdf/.ldf files are not found.

Then I went on specifying a 'WITH MOVE' options in the restore command in order to specify the location of the .mdf/.ldf file over a network share but I think the production server locks these file and prevent any read on them from the test server.

So basically, do you know a straightforward (and efficient) way to dump a database to a single file to easily load it in our test environment ?
Of course without disrupting the production environment.

Edit:

Both production and test SQL Servers version are 12.0.2000.

But I'm first testing loading from production to a local development SQL Server Express (also v12.0.2000)


texts: cardinality & column order in compound index (MySQL/InnoDB)

texts:
I know the column order in a compound index matters if you want to query by a subset of the indexed-columns, but if you're query specifies values for all the indexed columns, is there any performance benefit to having a high-cardinality column earlier than a low-cardinality column? I vaguely remember reading something that suggested that was the case because it narrows down the result set quicker, but I can't find anything to back that up now.

I'm using MySQL w/ InnoDB. InnoDB uses clustered indexes, which might be relevant to my question, but I think it only does so for primary keys, which my index is not. The table looks something like this:

CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ref_a_id` int(10) unsigned NOT NULL,
  `ref_b_id` int(10) unsigned NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_my_table_on_ref_a_ref_b_is_active` (`ref_a_id`, `ref_b_id`, `is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=2818259 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

In relation to my question, imagine ref_a_id has a higher cardinality than ref_b_id.


texts: How can I ensure that in a insert trigger a column gets filled before other column?

texts:
The bigger picture:
tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each pipeline into segments of maximum 1 meter distance. Then, for each segment I am doing a spatial query that selects the closest street (in a maximum 20 meter range). Then, by finding out which street appears the most in all the segments that belong to that specific pipeline(by id), I am inserting the found street name into the pipeline feature.

In a trigger (on insert, update) the function is making a join based on the unique id column.

Having tableX (id, name)

the function that gets triggered looks like this:

CREATE or REPLACE function T_function() RETURNS trigger AS $T_function$
BEGIN
NEW.name =(SELECT ......... WHERE other_table.id = NEW.id .....);
RETURN NEW;
END;
$T_function$ LANGUAGE plpgsql;

As you can see, the function does a join based on the newly created id. It works only on update, probably because the NEW.name does not use the new id. How can I enforce saving/using/generating NEW.id before NEW.name ..?

later edit describing the tables (with postgis geometry) and what I am trying to do:

CREATE TABLE tableX (
    id integer,
    name varchar,
    shape geometry
);
--   geometry - linestrig

CREATE TABLE tableY (
    id integer,
    name varchar,
    shape geometry
);
-- geometry - linestring

CREATE or REPLACE function T_function RETURNS trigger AS
$T_function$
BEGIN
    NEW.id = NEW.id;
    NEW.name = (
        SELECT name FROM (
            SELECT id, name, cnt
            FROM (
                SELECT
                    id,
                    name,
                    COUNT(*) AS cnt,
                    RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
                FROM (
                    SELECT loc.id, r.name, ST_Distance(r.shape, loc.shape) AS dist
                    FROM (
                        SELECT id, (st_dumpPoints(st_segmentize(shape, 1))).geom AS shape
                        FROM tableX
                    ) as loc
                    INNER JOIN tableY AS r ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE loc.id = NEW.id
                    ORDER BY loc.id, ST_Distance(loc.shape, loc.shape)
                ) AS coco
                GROUP BY id, name
            ) AS tg
            WHERE rnk = 1
        ) as b
    );
    RETURN NEW;
END;
$T_function$
LANGUAGE plpgsql
;

CREATE TRIGGER T_function_trigger
BEFORE INSERT OR UPDATE ON tableX
FOR EACH ROW EXECUTE PROCEDURE T_function()
;

I am trying to do a spatial query and to fill tableX.name column with the name from tableY.name column based on the nearest neighbor approach. It only works on update for now.


texts:
I am aware of the Microsoft provided code to transfer logins between SQL servers, however this only does the account and the password.

What if that particular account has various roles and permissions assigned to it at a server level, is there an equivalent piece of code to script these permissions also?

Thanks


texts:
Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth).  The public space is where I place all my default course material (tables, views, etc), most of which can be accessed by anyone interested.

What I would like is for:

each student (any authenticated user) to be able to create their own schema and use that as the scope where they can create tables and views
students to not be able to create tables/views/objects in public
to apply these settings to all all users; or more specifically, not have to target specific user accounts when assigning permissions

Thought process:

REVOKE ALL ON DATABASE class FROM PUBLIC
GRANT CREATE ON DATABASE class TO PUBLIC  -- can it be CREATE SCHEMA ?
-- being able to create on database makes it seem like they can create in public
-- can permissions be revoked from public?

As user:

psql -h <host> class

-- desire: permitted
CREATE SCHEMA <current_user>;
GRANT USAGE ON <current_user> TO teacher;
CREATE TABLE <current_user>.<table name> ...;
SELECT * FROM public.<table name>;
SELECT * FROM <current_user>.<table name>;

-- desire: not permitted
CREATE TABLE public.<table name> ...;

I'm not sure those are the proper permissions.  I was also curious if I could create a roll and use the role to administer permissions.  The tricky bit is I would like any authenticated user to be part of that role, which I don't think you can add public to a role, so I don't know how that would work.

I haven't thought this through thoroughly, but appreciate any feedback, concerns, and answers offered.  Of course, I'm available to answer questions for clarification -- being concise while still offering comprehensive details is a challenge.


texts: How to get postgres to resume processes after it hangs when the RAID drive hosting the tablespace times out

texts:
Nearly once per day I'm getting a timeout that shows up in console like:

7:1:36 PM kernel: Areca RAID volume scsi command timeout (target=0 lun=0)

which corresponds exactly to when all postgres processes hang.

I'm reluctant to terminate pids because that tends to bring everything down.  Is there anyway to resurrect these processes?  I can't even open a new postgres window in terminal.

Also, is there any way to make postgres more tolerant of such timeouts?
My System:

Mac Pro (2013) with 12 core X 64GB
OSX 10.10.4
Postgres 9.4.4

the drives I'm using for tablespaces (4 of them) are:

Areca 8050 T2
each with 8 1TB OWC Mercury Electra 6G SSDs in a RAID 0 config.


texts:
Is it advised to use SSRS in the database server itself? Will it hamper the performance of the server itself. (in SQL SERVER 2005)
What are the merits and demrits of the same?


texts: Can't create a view for other user's table despite having select privilege

texts:
I already executed the following commands:

grant create view to scott;
conn hr/hr;
grant select on employees to scott;

but I conn to scott:

create or replace view company_phone_book as
select employee_id emp_id, last_name||','||first_name name, phone_number, email
from hr.employees;

gives me the error:

  ORA-01031: insufficient privileges.


texts: Table view created with triggers, someone please explain why?

texts:
I'm part of a project that inherited a piece of software that has a unique MS SQL database design that I'm trying to understand why they designed it the way they did.

Basically for every table, for example table tab_user, there is a duplicate table that is called tab_user_view.  The tab_user_view is populated with triggers placed on the tab_user table to populate the tab_user_view. All of our pages that list data retrieve data from the _view tables. I kind of understand this is to avoid locking and increase performance, but why wouldn't they just use traditional views?  The original software was written in 2003 so is it possible some tech wasn't available that we now have? Thanks

UPDATE

-Yes, there is some data manipulation for the _view tables, but the same thing can be achieved with views, so why?


texts:
Lets say I have a table X with following definition:
CREATE TABLE X( id INT PRIMARY KEY, field1 varchar(100), field2 int, field3 double);

Then I issue a query:
SELECT field1, field2 from X;

Let's say this query returned 10 records.
Is there a way to write a query to update the record #5 from the SELECT? What would the query will look like? Or I will have to include id in that query, somehow find its value in a record #5 and then use it in a query below?
Something like UPDATE X SET field1 = 'def' WHERE....
I have a problem with the condition - what to write for the WHERE clause. I'm working with multiple DBMSes, so need a generic way if possible.
More details:
I can have a query like this:
SELECT field1, field2 FROM X WHERE field3 = "abc";

or I can have a query like this:
SELECT X.field1, Y.field2 FROM X, Y WHERE X.pk = Y.pk AND X.field3 = "abc";

Let's say when I skip to record 5, X.field1 will show "abc". I want this field to be updated to "def". So I type "def" and skip to record 6. In both cases I want to update record 5 X.field1 of the resulting recordset.
As you can see I'm going by the records in a recordset (results of executing a query). I want to emulate what Access is doing.
Let's say I open the new form in Access and I base it on the query.
I re-arrange the field on the form to my liking and execute the form.
Then I go to display the record #5 in the form and then update a field.
This update is instantaneous, no matter the complexity of the query behind the form.


texts: Comparing 1 Database with Many Tables to Multiple Databases with Fewer Tables in Each

texts:
What are the pros and cons to creating one huge database with numerous tables or better separating the data into many databases that each have fewer, more relevant tables?

Are there server performance issues associated with either design?
Are there memory issues with either design?
Are there connection issues with either design?

(FYI, I am designing my data store in MySQL, but general conceptual viewpoints are greatly appreciated)

Thanks.


texts: Using table inheritance instead of mapping tables

texts:
This seems like a pretty common scenario: several types that all compose the same child type.

This could typically look like so:

-- 'name' is unique per parent record
CREATE TABLE sometype (
  sometype_id serial PRIMARY KEY,
  name        text
);

CREATE TABLE foo (foo_id serial PK);
CREATE TABLE bar (bar_id serial PK);

CREATE TABLE foo_sometype (
  foo_id      int4,
  sometype_id int4
);

CREATE TABLE bar_sometype (
  bar_id      int4,
  sometype_id int4
);

Which is fine, but cumbersome to query. I'm thinking this could be cleaner:

-- 'name' is unique per parent record
CREATE TABLE sometype (
  name        text
);

CREATE TABLE foo_sometype (
  foo_id      int4
) INHERITS(sometype);

CREATE TABLE bar_sometype (
  bar_id      int4,
) INHERITS(sometype);

What I like about this:

simple to join (with USING)
no need to add a surrogate key to 'sometype', it is explicitly a component of 'foo' and 'bar'

Seems like an atypical use of inheritance, though.

Any reason not to do this?

On the "deficiencies" of inheritance

Note that the standard caveats to Pg inheritance are only relevant when table inheritance is used to directly model class inheritance, which is not what I am doing here. In fact, for this to work I need inheritance to behave the way it does.

I almost wish they called it something other than "inheritance", since the behavior is quite logical, and the "shortcomings" are only relevant to one use-case.

Benefits over manually duplicated table structure

As Evan points out, I could just manually create 'foo_sometype' and 'bar_sometype' that look exactly like what I describe, but I think there are a couple of significant benefits to the inherited structure:

The inherits relationship explicitly defines 'foo_sometype' and 'bar_sometype' as being of the same type, not just two tables that happen to have the same columns.
Making future schema changes through the parent table lessens the chance of accidental divergences (with a little work, this could actually be enforced).
More importantly, client code can be generated against the parent table, and applied to the children with only a change in the table name, with (again) confidence that the table structure is enforced.

So, as a contrived example, Foo and Bar could have a HasSomeTypeList trait, which abstracts all 'sometype' operations, and knows that both tables can be mapped to the SomeType class.

Representing the Foo/Bar relationship, whether it's modeled as a trait or as inheritance, is the ultimate goal here.

Incidentally, to the naive user/query-writer - who is not expected to make schema changes - these two ways will look the same.


texts: Should i expect to execute of 160billion iteration from oracle 11g?

texts:
I have a table that contains approximately 390 000 records and up to now I found 3 different algorithm(solution) for my problem. Each algorithm require cartesian product of table with itself.
I am wondering only that; should i expect from oracle 11g to execute a query requires 160 Billion iteration or what? Because for one week I try a lot of things but whatever I do it is not end even after 8 hour execution.

If this is normal for this much process I maybe start thinking of a new algorithm because I must ended up in approximately 30 minutes.

My last and better among three algorithm is;

declare
loopCount number(10);
remainingRows number(10);
dup number(10);
crosses number(10);
res varchar2(5);--TRUE or FALSE
BEGIN
loopCount :=0;
remainingRows :=0;
crosses :=0;
dup := 0;
res :='FALSE';
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;--376497 records
WHILE(remainingRows ^= 0) LOOP
 FOR aRow IN (SELECT mip,startmi,mi_prinx,geoloc,rownum FROM ORAHAN where rownum=1)  LOOP
  FOR bRow IN (SELECT mip,startmi,mi_prinx,geoloc FROM ORAHAN WHERE Mi_Prinx ^= aRow.Mi_Prinx) LOOP
    BEGIN
      --loopCount := loopCount+1;
      select SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) into res from dual;
      IF (res='TRUE') THEN
        Insert INTO ORAHANCROSSES values (aRow.Mip,aRow.Startmi,bRow.Mip,bRow.Startmi);
        DELETE FROM ORAHAN WHERE MI_PRINX=bRow.MI_PRINX;
        COMMIT;
        crosses := crosses+1;
        END IF;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          dup := dup+1;
    END;
    remainingRows := remainingRows-1;
  END LOOP;
DELETE FROM ORAHAN WHERE MI_PRINX=aRow.MI_PRINX;
END LOOP;
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;
END LOOP;
dbms_output.put_line('crosses: ' || crosses);
dbms_output.put_line('duplicate: ' || dup);
END;

I execute this and in 3 hour it is delete 604 record from source table and insert 99 row to target table. There is 376342 remaining rows.


texts:
I've got a question about Galera cluster + MySQL/MariaDB and RAM.

Basically, our MySQL cluster needs to run in-memory.

But:

We have 3 TB of data;
We don't have 100,000$ for RAM :)
We need High Availability.

In this case, is Galera a good solution? Will it run with 3 nodes (each with 128GB RAM and SSD's)?


texts: DBO execute returns true but doesn't execute

texts:
I am having a bit of trouble with some of my code. This is the code:

$STMT3 = $PDO->prepare('
   INSERT INTO `lotteries_entries` (steamID, lottery_id) VALUES (:STEAMID, :ID);
   UPDATE `lotteries_entries` SET `winner` = 1 WHERE `lottery_id` = :ID ORDER BY RAND() LIMIT 1;
   UPDATE `lotteries` SET `status` = 1 WHERE `id` = :ID;
   INSERT INTO `lotteries` (status, type) VALUES (0, :TYPE);'
);

$STMT3->bindParam(':STEAMID', $_SESSION['STEAM_steamid'], \PDO::PARAM_INT);

$STMT3->bindParam(':ID', $result['id'], \PDO::PARAM_INT);

$STMT3->bindParam(':TYPE', $result['type'], \PDO::PARAM_INT);

$STMT3->execute();

Although the $STMT3 returnes true (by using var_dump to check it), it unfortunatly never Inserts or Updates any of the tables it was told to. I've tried to run the script on the MySQL "SQL" tab (Directly through SQL) and it work fine. No errors are thrown through the script, and the SQL code isn't problematic.

Please not i use transactions, and yes i have started an commited the transaction. Both start and commit is reached by the script without any difficulty.

To see the whole code please visit: https://gist.github.com/vasil7112/8913f002940aaeb2f682

Kind regards and wish you have had a wonderful year.


texts: How to save and then restore permissions after refreshing a database

texts:
A database in a STAGING environment is overwritten every N weeks with a PRODUCTION copy that is more recent.

What is the proper way to save and then restore users, roles and explicitly granted permissions as they were before the existing database was overwritten by the production copy?

I do not want to restore users and permissions as they were in PRODUCTION, but as they were in the DEV environment before the database was overwritten.

This refresh process must be automated as there are many different environments with dozens of instances that can potentially host up to 10 databases.


texts: Does an ad hoc, full backup, using Oracle RMAN, flag files, so they are not backed up by the regular backup?

texts:
If I run a simple RMAN full backup, ad hoc, outside of the regular backup scheduled, does that flag files so when the regular backup runs, that regular backup won't get some of the log files?

For example,

RMAN> Backup Database;


texts: Oracle Database 11G Installation "Error al llamar al destino 'install' del archivo make"

texts:
I get this error installing Oracle Database 11g in Linux..

INFO: /usr/lib64/libstdc++.so.5: referencia a `memcpy@GLIBC_2.14' sin definir

INFO: collect2: error: ld devolvió el estado de salida 1

INFO: make: *** [ctxhx] Error 1

INFO: Salida final del proceso iniciado.
INFO: ----------------------------------
INFO: Excepción devuelta de la acción: make
Nombre de la Excepción: MakefileException
Cadena de la Excepción: Error al llamar al destino 'install' del archivo make '/o/app/oracle/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk'. Consulte '/o/app/oraInventory/logs/installActions2018-02-06_08-13-05AM.log' para obtener más información.
Gravedad de la Excepción: 1


texts: Why is the optimizer doing a table scan vs Index Seek

texts:
Probably a straight forward answer, but I'm too bleary eyed to see it.

Have a simple query that is pulling the top 100 rows based on a char column.

The column has an index on the where column.
Returning all the columns (*)
There are ~100 millions rows
Statistics have been updated

The optimizer thinks a table scan is more efficient than running an index scan.

Sample Table:

CREATE TABLE [dbo].[tst](
[Mon] [char](6) NULL,
COL1 [varchar](50) NULL,
COL2 [varchar](50) NULL,
COL3 [varchar](50) NULL,
COL4 [varchar](50) NULL,
COL5 [varchar](50) NULL
)

Sample Query:

select
   top 100 *
from
   <table>
where
   mon = '201509'

This will take > 30 seconds to return since the optimizer chooses a table scan vs using the index seek. An index scan is < 1 sec.
Interestingly if a symbol is added in the where
e.g. '2015.09' or '2015 09'

It will use the index.

Searching straight '201509', it uses a table scan.

Add in a dot or space, it uses an index scan.

Can someone explain to me why the optimizer is choosing a table scan vs using the index?

Edit: Thanks all for the excellent feedback and information. I didn't know about traceflag 9130 and from that I can see the estimated rows are a lot less than the actual rows which is likely why the optimizer is choosing the table scan vs the index.

Here is the Statistics Output:
Without index hint:

Table 'TestTable'. Scan count 1, logical reads 982046, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 17437 ms,  elapsed time = 17792 ms.

With Index hint:

Table 'TestTable'. Scan count 1, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.


texts: How do I find a job that sends a specific email?

texts:
I had a job sending an irrelevant email to a group of users.
The title of the email was Staging -> [AUPAIR] arrivalDate trigger issue.
When I query the jobs table in the msdb database using the title above, I got no answer:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
WHERE    js.command LIKE N'%Staging -> [AUPAIR] arrivalDate trigger issue%'

Only when I alter my query as below, could I find the bandit job:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
--WHERE    js.command LIKE N'Staging -> [AUPAIR] arrivalDate trigger issue%'
WHERE    js.command LIKE N'%Staging -> %'

This is what was the command on the jobstep:
if exists (
select count(1) as total
,year(modify_date) as [Year]
,month(modify_date) as [Month]
from repl_aupair r
where r.arrivaldate is not null
and junocore_applicationid not in(
select applicationid from junocore.dbo.replicationUpdateControl
where arrivaldatechanged = 1 )
and modify_Date >='2017-01-26 00:38:46.197'
group by year(modify_date),month(modify_date)
having year(modify_date) >= 2017
--order by [Year] desc
)
begin
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'DBA',
            @recipients = 'my_emails@mycompany.com',
            @body = 'APIA_Repl_Sub.dbo.repl_aupair arrivalDate not null and replicationUpdateControl arrivalDateChanged still 0',
            @subject = 'Staging -> [AUPAIR] arrivalDate trigger issue ';
end

Why did my first query not work?
Conclusion:
I modified my script and the following two versions work!
After the answer by EzLo:
USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'


texts: SQL Server Database Table Partitioning Consideration

texts:
I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and moved into a data warehouse database, which is done using stored procedure and this involves complex join on other staging tables of similar size.

Below is the structure of staging table:

Id
SystemCode
TransactionDate
ProcessStatus
DataField1
DataField2
DataFieldN

I am thinking about partitioning the staging tables on the SystemCode field. I have around 40 distinct systems from which data will be loaded. Once the records are processed, 'ProcessStatus' flag will be updated to prevent further processing.

Does anyone think this is a bad idea? My aim is to have them loaded as quickly as possible into data warehouse. I have considered indexes and the result was not great. I can't partition on date as the load to warehouse won't be based on this criteria.

Appreciate your help. Thanks.


texts: Sequential scans instead of index with a PostgreSQL conditional index

texts:
I have the following PostgreSQL table, in it I store millions of rows.

I'm effectively trying to use it as a work queue, large lumps of JSON go in (status=queued) and get processed (status=processed).

\d item_uploads;
                                           Table "public.item_uploads"
     Column      |            Type             | Collation | Nullable |                  Default
-----------------+-----------------------------+-----------+----------+-------------------------------------------
 id              | integer                     |           | not null | nextval('item_uploads_id_seq'::regclass)
 user_id         | integer                     |           |          |
 last_timestamp  | timestamp without time zone |           |          |
 input           | text                        |           |          |
 status          | character varying           |           |          | 'queued'::character varying

Indexes:
    "item_uploads_pkey" PRIMARY KEY, btree (id)
    "index_item_uploads_on_user_id_and_last_timestamp_and_id" btree (user_id, last_timestamp, id) WHERE status::text = 'queued'::text

For most of the time all but a few of the rows in the table will be status=processed.

I regularly run the query:

SELECT 1 AS one
FROM   "item_uploads"
WHERE  "item_uploads"."user_id" = 242
       AND "item_uploads"."status" = 'queued'

What I'm noticing is that occasionally (often after an ANALZYE) Postgres will flip to a sequential scan.

My hope in using this conditional index was that it would always hit the index when I search for queued. Is this an unreasonable expectation? Obviously with the millions of non-"queued" rows what should be a cheap query becomes very expensive.


texts: In the Vertica database, what is a namespace?

texts:
In the Vertica database, what does the term "namespace" mean?

I have reviewed the entire Vertica documentation and cannot find what this means.


texts: SqlLocalDB 2014 can not find SqlUserInstance.dll

texts:
Everytime I run the command: SqlLocalDB i get this error: SqlUserInstance.dll not found! Windows error code: 126.

I searched but I could not find anything on what to do. I repaired the SqlLocalDatabase 2014 express but that didnt help either

EDIT: I just took a look and its under: C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn


texts: postgres backup for specific table with blob oid

texts:
I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 entries in pg_largeobject table). I read the following link and added -o and -b to the backup parameters.

https://www.postgresql.org/docs/9.1/static/app-pgdump.html

I am using below command to take backup.

 pg_dump -i -h myhost -p 5432 -U myuser -F c -v -t schema.my_table -o -b -f mytablebkp.backup "mydb"

Is anything wrong with that command? How can I take a backup of only the specific table with only those blobs that belong to it?

Update:

I read the following from documentation (https://www.postgresql.org/docs/9.5/static/app-pgrestore.html),

pg_restore cannot restore large objects selectively; for instance, only those for a specific table. If an archive contains large objects, then all large objects will be restored, or none of them if they are excluded via -L, -t, or other options

Since pg_restore can not do selective restore, is there no way / possibility to take selective table backups with blob / oid ?


texts:
I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how I'm trying (and failing):

select nm_linha from linhas where cidades_origem like '%SUBQUERY HERE WOULD GO HERE%'


texts: For MySQL, is there tool to dump a set of rows from one tables and all related (by foreign key) rows from all related tables?

texts:
I'm using MySQL db.

I want to dump a set of rows from one table and if these rows have foriegn keys that point to rows on some other tables, I want to dump those too.

Then I want to load these dumps into their respective tables on a different database (e.g using LOAD DATA INFILE command). So simply using select...join...into outfile is not enough since the result would be one big joined table.

For example table A has B_id and C_id columns that point to rows on table B and C respectively. So when I dump 3 rows from table A, 3 related rows from B and 3 from C are dumped too.

Does such a tool/script exist?

Writing a script to do that is very doable, but I don't want to reinvent the wheel if such a tool already exist.


texts:
I am using the below command in my batch file and running it via task scheduler.
osql -S myserver\sqlexpress -E  -i "c:\test.sql" -o "c:\output.txt"

I get the error stating that:

Server is not found or not accessible. Check if instance name is
correct and if SQL Server is configured to allow remote connections.

Could someone advise what exactly should be the myserver and sqlexpress in the above example?
I am referring the link for the above script.
How to automatically execute SQL queries with a batch file
Source: Mitel MiContact Center Knowledge Base


texts: Can not re-set [log_reuse_wait] after log back up

texts:
I have a SQL Server 2005 database in full recovery mode with the following stats:

name XX
state    0
state_desc   ONLINE
size 2620152
max_size -1
growth   128

Log File

name XX_Log
state    0
state_desc   ONLINE
size 517832
max_size 268435456
growth   64000

Log file stats

Database Name    XX
Log Size (MB)    4045.555
Log Space Used (%)   0.7804911
Status   0

So I would like to reduce the size of the log - I think my target should be 50MB? with growth of 50MB - so I did a full back up - followed by log back up, twice. Without success

Having done some reading I check my  [log_reuse_wait]  and got a 2 result - back up log.
So first question why is that, when I have just done two backups?

I have looked for open transactions and got the message

  No active open transactions. DBCC execution completed. If DBCC
  printed error messages, contact your system administrator

And I have checked the Activity monitor and looked at Blocked Header and can not find at 1 in any session - I believe that indicates a long running transaction

Finally all I know is when I run DBCC LOGINFO I get a return of 390 lines which I believe indicates too high a VLF count?

I believe the count too high from the articles I have read (but maybe not fully understood) the articles are transaction-log-vlfs-too-many-or-too-few  and a-busy accidental-dbas-guide-to-managing-vlfs

I appreciate that I could take the database offline and deal with the problem as detail here and other places, but I think the log file will just grow again and this is our main production database so downtime is too be avoided if possible

What do people suggest as next steps please?

Thanks
Ian


texts: How to give a client backups of their data without compromising our database design?

texts:
The client is asking for backups of their data, however we would not like to share our database design as it may lead to a security risk...

inb4: Security by Obscurity is not a valid security control method.

I have thought about dumping only pertinent tables with their data in csv or excel xml form. What is the correct and most acceptable way without giving our whole schema away?


texts: In Oracle, what grants do I need as a non-schema user to view rows in all_objects?

texts:
I have two instances of an oracle 11g database, both have the same object definitions in the schema: tables with indexes and constraints, a sequence, and a stored procedure.

The facts of the case:

The first instance I login as the schema user "MYDBOWNER".
The second instance I login as a role-permissioned user "MYDBUSER", though the schema is still owned by "MYDBOWNER".
I run the following query to see the objects owned by user "MYDBOWNER":

  select * from all_objects where owner='MYDBOWNER';

As MYDBOWNER, I get a list of all the objects including the indexes (but not the constraints).
As MYDBUSER, I don't see the indexes, but I see the table, sequence and procedure.

The permissions for the role is correct (table has select, delete, update and insert, sequence has select, and procedure has execute). So why don't I see the indexes? Is there an additional grant that needs to be provided to the role for the table?


texts: What is the deal with the "invisible schema" pg_catalog?

texts:
If I run this query:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes ORDER BY schemaname ASC, tablename ASC, indexname ASC;

It returns a ton of records for the nonexistent schema "pg_catalog". It's not a schema in my database. I assume it's some kind of internal, "secret" schema, but I find no mention of it in the PG manual when I searched for it before asking.


texts:
Whenever a user updates his profile, we conduct a set of queries like so:

DELETE FROM `user_likes` WHERE `ID` = $id
INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name)

Is there a way to do this all in one step? There has to be a way, because the interest IDs are all different for each user. Keep in mind that the data never actually has to be deleted - it's a set that is always growing.


texts: Primary key type change not reflected in foreign keys with MySQL Workbench

texts:
I have a problem with MySQL Workbench and primary/foreign keys.

I have some tables with PKs involved in relationship with other tables. If I modify the type of the PK, the type of the FK doesn't automatically update to reflect the change.

Is there any solution? Do I have to manually modify all the relations?


texts: How long is the output of EncryptByPassPhrase(), relative to the input?

texts:
I'm starting an encryption project, using EncryptByPassPhrase(), mostly on VARCHAR values.  Naturally, the encrypted values are longer than the originals.  Is there a formula I can use to calculate how long I need to make the new VARBINARY fields, so as to hold any possible value of the original VARCHAR field?

E.g., the first field I examined has values of up to 37 characters in length, and the encrypted values are up to 100 bytes; another has values up to 50 chars and encrypted values are up to 124 bytes.  However, values as short as two or three characters can encrypt to 76 bytes.  If I size the new fields as 75 + X bytes, will I have room to store the encrypted version of any possible text value of length X or less?


texts:
I'm seeing from Perfmon that %C2 Time is being primarily used instead of %C1 Time in our Azure VM that is used for SQL Server. Normally if the host was on something like VMWare, I could get the Admins to change the Power setting from Balanced to High Performance within the Hosts BIOS.

Does anyone know if this can be done or even if this Processor State Transition time is a concern on the Azure environments?

Thanks


texts: Big Data storage, for relative data storage

texts:
I have 2 tables in MySQL DB.

account - Around million records and increasing

id
name

proposal - 5,000 records and increasing

id
name

I have some algorithm that runs and find fit between account and proposal I want save the matches in DB, that will be filled from some queue manager, etc. So I can fetch the proposals much faster.

So that table will look like

account_proposal

account_id
proposal_id

As I understand I need to save at most 1M X 5K records I think it will be too heavy for MySQL and I want to solve the problem for future, when I will have 30M Accounts and 0.5M proposals.

So my question is which db engine fits more for saving such data?


texts: sorting groups of related rows by average values while keeping the groups together

texts:
I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible.
Consider the following table.

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

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

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

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

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

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

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


texts:
I got such a deadlock:

I see that /process/@lockMode is equal to /owner/@mode.
But why /keylock/@mode is not equal to /owner/@mode?
What does /keylock/@mode mean?

As I understand from the entire deadlock, processVictim should neither use U lock nor X lock.

Here is the entire deadlock:

<deadlock-list>
 <deadlock victim="processVictim">

  <process-list>
   <process id="processVictim" taskpriority="0" logused="0" waitresource="KEY: 10:72057602742812672 (3100aad8604d)" waittime="2982" ownerId="222541347" transactionname="user_transaction" lasttranstarted="2018-04-28T00:17:29.680" XDES="0x7d6dcf990" lockMode="S" schedulerid="13" kpid="14052" status="background" spid="25" sbid="0" ecid="0" priority="0" trancount="1">
    <executionStack>
     <frame procname="Db1.notify.pRuleActivated" line="71" stmtstart="3296" stmtend="3508" sqlhandle="0x03000e00d9368812ce87f700aaa800000100000000000000">
if exists(select 1 from DbRepl.dbo.vRule where RuleId = @nRuleId_ and CloseDate is not null)     </frame>
     <frame procname="Db1.notify.pRuleActivatedHandler" line="21" stmtstart="928" sqlhandle="0x03000e000f89bc79375d210165a100000100000000000000">
EXEC notify.pRuleActivated
    @xmlMsg = @xmlMsg     </frame>
     <frame procname="Db1.notify.pQppQueueProcNotificationProcessingQueue" line="105" stmtstart="5906" stmtend="6154" sqlhandle="0x03000e0020de2a3dd505d00096a100000100000000000000">
exec @proc_ @xmlMsg = @msg_, @uuidConversationGroup = @cgid_, @uuidConversationHandle = @dh_, @nServiceId = @service_id_;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>

   <process id="processSurvivor" taskpriority="0" logused="149676" waitresource="KEY: 10:72057602742681600 (460029689516)" waittime="2904" ownerId="222536438" transactionname="user_transaction" lasttranstarted="2018-04-28T00:16:28.673" XDES="0x17ea1c780" lockMode="X" schedulerid="12" kpid="9864" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-04-28T00:17:34.753" lastbatchcompleted="2018-04-28T00:17:34.710" isolationlevel="read committed (2)" xactid="222536438" currentdb="10" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128056">
    <executionStack>
     <frame procname="DbRepl.dbo._pReplUpd_dbo_tRule" line="34" stmtstart="1514" stmtend="6938" sqlhandle="0x03000a0067d8b8712176bf00c0a800000100000000000000">
update [dbo].[tRule] set
        [Id] = case substring(@bitmap,1,1) &amp; 2 when 2 then @c2 else [Id] end,
        [TypeId] = case substring(@bitmap,1,1) &amp; 4 when 4 then @c3 else [TypeId] end,
        [EventId] = case substring(@bitmap,1,1) &amp; 8 when 8 then @c4 else [EventId] end
where [RuleId] = @pkc1     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 10 Object Id = 1907939431]    </inputbuf>
   </process>
  </process-list>

  <resource-list>
   <keylock hobtid="72057602742812672" dbid="10" objectname="DbRepl.dbo.tEvent" indexname="PK_tEvent" id="lock261441400" mode="X" associatedObjectId="72057602742812672">
    <owner-list>
     <owner id="processSurvivor" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processVictim" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

   <keylock hobtid="72057602742681600" dbid="10" objectname="DbRepl.dbo.tRule" indexname="PK_tRule" id="lock476f3d680" mode="U" associatedObjectId="72057602742681600">
    <owner-list>
     <owner id="processVictim" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processSurvivor" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

 </deadlock>
</deadlock-list>

And it's visualization:


texts: how to cope with datatype validation errors in ssis 2005

texts:
An error occurs within different components (ForEach & SQL Task) when applying variables such as User::cnt and User::sum for instance.

But there is manually no mismatch to see regarding datatypes.
Is there a missing SQL Server 2005 Hotfix to install or another frequently occuring open issue to resolve?

  [Execute SQL Task] Error: An error occurred while assigning a value to
  variable "sum": "The type of the value being assigned to variable
  "User::sum" differs from the current variable type. Variables may not
  change type during execution. Variable types are strict, except for
  variables of type Object. ".

It works fine with SSIS 2012 but it is a requirement to deliver the same stuff in SSIS 2005. This is the reason concerning the old application version of SSIS.

Resolved: DB attribute was an int and SSIS required a LONG


texts:
In my previous work station, I saw them have a table design across all website they have worked on displaying editor content like this:

column|page_id| title |content_1|... |content_n| lang  |update_date|...
-----------------------------------------------------------------------
row   |   1   |varchar|   text  |text|  text   |tinyint|  datetime |
......

  where content_n column are determined by the max editor it overall
  has on that website.

  For an example: If the website generally has 1 editable content per
  page but in addition, there is one page that has 4 editable content,
  they will have 4 content_n column.

The problem(imo):

For pages that have only 1 editable content area, only content_1 will be filled, and other column content_n will be empty. In addition, with multiple languages involve, those empty field add up.

My question:

Is this a good design of choice?
Wouldn't those empty column create unnecessary overhead?
What would be an improved table design when have a table such as this?

My thought :

I will probably merge all the content and title into one single column(mediumtext/longtext) and use json_encode/array to differentiate the content.

they usemysql(MYISAM) for all database engineer, and I prefer Innodb..if that is matter


texts: Percona Xtradb Cluster : How to speed up insert?

texts:
I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 insertions every 5 minutes, but also made around 400 update in the same time. All this operation take less than 3 minutes when i was on a single server architecture. And now, with 3 node server, it takes more than 5 minutes ...

Is there any tuning i can do to speed up this operations ?
Here is my actual cnf configuration :

[mysqld]
datadir=/var/lib/mysql
user=mysql

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://dbnode01,dbnode02,dbnode03

binlog_format=ROW
default_storage_engine=InnoDB
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_node_address=1.2.3.4
wsrep_cluster_name=my_cluster
wsrep_sst_method=xtrabackup
wsrep_sst_auth="user:password"

Here are the 3-server hard config :

Node#1

CPU: Single Processor Quad Core Xeon 3470 - 2.93Ghz (Lynnfield) - 1 x 8MB cache w/HT
RAM: 8 GB DDR3 Registered 1333
HDD: 500GB SATA II

Node#2

CPU: Single Processor Quad Core Xeon 1270 V2 - 3.50GHz (Ivy Bridge) - 1 x 8MB cache w/HT
RAM: 4 GB DDR3 1333
HDD: 1.00TB SATA II

Node#3

CPU: Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz (4-Cores)
RAM: 32G
HDD: 2T

UPDATE

Actualy there's around 2.4M records (24 fields each) in the table concerned by the INSERT/UPDATE statements (6 fields indexed).


texts:
I'm managing a Magento server with MySQL installed in it. 16GB RAM 8 core vCPU (Digitalocean).
We're having a problem with the site becoming a bit sluggish when under medium to heavy load. Under low to none, MySQL uses around 25% to 60% of CPU (according to HTOP). Under medium to heavy load MySQL takes up 150% of CPU usage (again, according to HTOP). On the graph CPU usage on all cores is stuck at 100% during medium usage of the website.
My MySQL configuration is the default, a performance tuning was never done. I'm working on configuring PHP and Elasticsearch memory/CPU usage, but I need help with MySQL configuration since I never dealt with public facing servers (web store).
This is what mysqltuner says:
[!!] Maximum reached memory usage: 154.1G (983.67% of installed RAM)
[!!] Maximum possible memory usage: 153.1G (977.27% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Highest connection usage: 100%  (152/151)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2B selects)
[!!] Joins performed without indexes: 600679
[!!] Table cache hit rate: 0% (2K open / 3M opened)
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 90.7% (378 cached / 35 reads)
[!!] InnoDB buffer pool / data size: 1.0G/1.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal to 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[!!] InnoDB Write Log efficiency: 8.97% (62690490 hits/ 699060007 total)

General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2419)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    table_open_cache (> 2419)
    innodb_buffer_pool_size (>= 1.2G) if possible.
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)


texts: How to pass a row as a parameter to a Postgres function?

texts:
I have a function build_details(), which takes as one of its parameters, a single row/record from another table.

This is what I want to do, but it doesn't work:

SELECT build_details(
    SELECT * FROM my_table LIMIT 1,
    1000,
    TRUE)

I want to take a single row from my_table and pass it to the function so that I can make it run. How do I call the function from the terminal with a record from my_table?


texts: Intermittent failures to connect to 127.0.0.1

texts:
I have an instance of SQL Server 2016 (13.0.4451) which I can reliably connect to over shared memory (lpc:(local)\instance).  If instead I try to connect to 127.0.0.1,1433 I experience low (~1/1000) failure rates with the error message:

  Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=14995;

What would affect the loopback connection, but not the shared memory connection?

Related, but far more broad: Connection Timeout Expired without apparent network issue


texts:
Is it possible to select all fields except few fields for a single table? (Suppose there are bundle of fields that writing each field in Select statement is time consuming).

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

Something like:

Select * Except (row_num) from Table

The above syntax is wrong because I get an error "select * must have a from clause"

So what is the appropriate syntax to get an output:

id  customer_code comments
-----------------------------------
1   Dilbert        Hard
1   Dilbert        Worker
2   Wally          Lazy


texts: Federated table of two LEFT JOIN tables not working

texts:
For some reasons I am getting the following error when executing the below query:
(What I am trying to achieve is to sync and merge two left-joined table to into one

  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 'ENGINE=FEDERATED' at line 35

CREATE TABLE users AS (
  SELECT
    ID, user_email, user_registered,
    first_name.meta_value as first_name,
    last_name.meta_value as last_name,
    telephone.meta_value as telephone,
    country.meta_value as country,
    company.meta_value as company,
    address.meta_value as address,
    city.meta_value as city,
    professional_title.meta_value as professional_title,
    state.meta_value as state,
    areas_of_interest.meta_value as areas_of_interest
    FROM wp_users
    LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
        AND first_name.meta_key='first_name'
    LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
        AND last_name.meta_key='last_name'
    LEFT JOIN wp_usermeta AS telephone ON telephone.user_id=ID
        AND telephone.meta_key='telephone'
    LEFT JOIN wp_usermeta AS country ON country.user_id=ID
        AND country.meta_key='country'
    LEFT JOIN wp_usermeta AS company ON company.user_id=ID
        AND company.meta_key='company'
    LEFT JOIN wp_usermeta AS address ON address.user_id=ID
        AND address.meta_key='address'
    LEFT JOIN wp_usermeta AS city ON city.user_id=ID
        AND city.meta_key='city'
    LEFT JOIN wp_usermeta AS professional_title ON professional_title.user_id=ID
        AND professional_title.meta_key='professional_title'
    LEFT JOIN wp_usermeta AS state ON state.user_id=ID
        AND state.meta_key='state'
    LEFT JOIN wp_usermeta AS areas_of_interest ON areas_of_interest.user_id=ID
        AND areas_of_interest.meta_key='areas_of_interest'
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION 'mysql://*********';

Note: FEDERATED Engine is on and working on other non-joined table.


texts: Does XP_API deprecation announcement mean that sp_executesql will not be supported in the future?

texts:
I have setup an Extended Events session in SQL 2008 R2 to track the deprecation_final_support and deprecation_announcement events.  I noticed some "deprecation_announcement" events in my log with "feature" of "XP_API", which appear to all be SQL statements that use SP_EXECUTESQL.

The event message says:

  Extended stored procedure API will be removed in a future version of
  SQL Server. Avoid using this feature in new development work, and plan
  to modify applications that currently use it.

One example of the sql_text action from an event is:

exec sp_executesql N'DECLARE @c varbinary(128);SET @c=cast(@userName as varbinary(128));SET CONTEXT_INFO @c;', N'@userName varchar(128)', @userName = 'MYDOMAIN\myuser';

The master.sys.SP_EXECUTESQL proc is listed under "Databases > System Databases > master > Programmability > Extended Stored Procedures > System Extended Stored Procedures" in Object Explorer.

Does this mean that sp_executesql will not be supported in a future version of SQL Server?


texts:
I setup master/slave replication of mongodb on EC2. But I see not replication happening. When I do "show dbs" on master, it shows all dbs expected.

But when I do the same on replica, it does not show me any db.

Please help me troubleshoot.

rs.config()
{
    "_id" : "ittw",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "ip-10-304-48-93:27017"
        }
    ]
}

rs.config()
{
    "_id" : "ittw",
    "version" : 2,
    "members" : [
        {
            "_id" : 0,
            "host" : "domU-17-31-19-16-88-5F:27017"
        },
        {
            "_id" : 1,
            "host" : "ec2-50-321-52-908.compute-1.amazonaws.com:27017"
        }
    ]
}

rs.status() // replica
{
    "set" : "ittw",
    "date" : ISODate("2013-08-12T06:55:57Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "$ip:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 356039,
            "optime" : Timestamp(1375934685, 1),
            "optimeDate" : ISODate("2013-08-08T04:04:45Z"),
            "self" : true
        }
    ],
    "ok" : 1
}

rs.status()  //Master
{
    "set" : "ittw",
    "date" : ISODate("2013-08-12T06:57:19Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "PRI_IP:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 356543,
            "optime" : Timestamp(1376289725, 1),
            "optimeDate" : ISODate("2013-08-12T06:42:05Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "REP_IP:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 355869,
            "optime" : Timestamp(1375934685, 1),
            "optimeDate" : ISODate("2013-08-08T04:04:45Z"),
            "lastHeartbeat" : ISODate("2013-08-12T06:57:17Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 1
        }
    ],
    "ok" : 1
}


texts:
We are application developers trying to decode why replication is failing.

It fails every week on the same day pretty much same time.

This has been happening for about a month now and our vendors are not helping with any answers.

Where should we start with diagnostics?


texts: Restore specific MSSQL backup from media set using AWS rds_restore_database

texts:
I have backed up several Microsoft SQL Server databases to a single media set (.bak file). I have uploaded the database to S3 and am now trying to restore the database using the following script where the database is named MyDB and the backup media set file is MyBackup.bak.
EXECUTE msdb.dbo.rds_drop_database N'MyDB'
EXECUTE msdb.dbo.rds_restore_database @restore_db_name = 'MyDB',@s3_arn_to_restore_from='arn:aws:s3:::dev-web/DbBackup/MyBackup.bak';

Unfortunately this just seem so to restore the first backup from the media set which is from another database. Is there a way to specify which backup I want to restore from the .bak file?


texts:
I have two tables to JOIN based on BETWEEN condition.

Table 1 is small is small around 1500 records and Table 2 is of 40 millions records. Table1 is having only one column with the datatype bigint and Table2 with 8 columns. I need to do join between these two tables on BETWEEN condition.

I tried following but its getting slow for just 1 record in Table1 and 40 millions in Table2.

Query:

SELECT t1.cola AS [InputValue],t2.cola,t2.colb,t2.colc,t2.cold,t2.code
FROM table2 t2
INNER JOIN table1 t1 ON t1.cola BETWEEN t2.cola AND t2.colb ;

Indexing:

CREATE NONCLUSTERED INDEX NCIX_Table1_Cola ON table1(cola)
CREATE NONCLUSTERED INDEX NCIX_Table2_Col_a_b ON table2(cola,colb)

Above query took 30 seconds for just 1 record in table1 and 40 millions in table2. As said I will be getting more than 1500 records in table1 will getting more slower. Any alternative of between or proper indexing need to be done?

Edit: Added sample data.

Table1:

cola
---------------
12
145
34
90
88990
987611
55
...
..
......1500 rows

Table2:

cola    colb    colc    cold    cole
-------------------------------------
0       10      c1      d1      e1
11      20      c2      d2      e2
21      40      c3      d3      e3
41      60      c4      d4      e4
61      100     c5      d5      e5
101     1000    c6      d6      e6
1001    10000   c7      d7      e7
10001   200000  c8      d8      e8
......
......40 millions records

Expected result:

InputValue  cola    colb    colc    cold    cole
--------------------------------------------------
12          11      20      c2      d2      e2
145         101     1000    c6      d6      e6
34          21      40      c3      d3      e3
.....

SQL Fiddle : Link


texts: Prevent T-SQL code that is not compatible to break the script

texts:
Here is an interesting challenge that I have not been able to crack... There is one nice T-SQL query that I Jonathan Kehayias created some time ago to find Implicit Conversions issues on the queries that I love. The thing is, this query doesn't work on databases with old compatibility levels (80 and before). I assume this is because Table Valued Functions (TVFs) where introduced in SQL Server 2005.

The problem is, if I use this script to validate implicit conversions on all my databases like this:

declare @sql    nvarchar(4000)
set @sql =
'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''?'' AND compatibility_level >= 90)
BEGIN
    USE ['+'?'+'] ;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET QUOTED_IDENTIFIER ON
    DECLARE @dbname SYSNAME
    SET @dbname = QUOTENAME(DB_NAME())

    BEGIN TRY
        RAISERROR(''?'', 0, 42) WITH NOWAIT;
        WITH XMLNAMESPACES
            (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'')
        INSERT INTO DMTAdmin.dbo.BestPractices_ImplicitConversions
        SELECT
            GETDATE(),
            @dbname,
            stmt.value(''(@StatementText)[1]'', ''varchar(max)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)''),
            ic.DATA_TYPE AS ConvertFrom,
            ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
            t.value(''(@DataType)[1]'', ''varchar(128)'') AS ConvertTo,
            t.value(''(@Length)[1]'', ''int'') AS ConvertToLength,
            query_plan
        FROM sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
        CROSS APPLY query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple'') AS batch(stmt)
        CROSS APPLY stmt.nodes(''.//Convert[@Implicit="1"]'') AS n(t)
        JOIN INFORMATION_SCHEMA.COLUMNS AS ic
            ON QUOTENAME(ic.TABLE_SCHEMA) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)'')
            AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)'')
            AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)'')
        WHERE t.exist(''ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]'') = 1
    END TRY
    BEGIN CATCH
    END CATCH;
END
'

exec sp_msforeachdb @sql

... the script will bomb because 9 out of my bizzilion databases are in compat level 80! You can easily try this by creating one database in a box with an old compat level and run this script, you will see that it will fail.

As you can see my script is using a TRY/CATCH, but that has not helped because the problem is actually a compilation error (and not a run time error).

Also I tried with an IF right at the beginning of that dynamic T-SQL (as you can see) but again, decision points will not prevent the code from being compailed for those databases and failing.

Nothing that I have tried so far helps me to avoid that error and as a result, the job I have reports as "failure" even when I'm OK with these specific databases being "skipped" if I could.

Do any of you guys have an idea of how could I implement this so that I could search on all but those old compat level databases?


texts:
Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:

measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve, and innodb_io_capacity to 50-75% of it, especially if your system is write-intensive.

But how do I measure these IOPS? What block size do I use (I assume the page size, so 16K by default)? Do I fsync after every block?
A fio command like this one gives me around 300 IOPS maximum, which seems very slow for these drives NVMe SSDs these are supposed to be. Most advice I can find online has these variables in the thousands.
fio --name fio --readwrite=randwrite --ioengine=libaio --direct=1 --filename=fio --numjobs=1 --size=4G --gtod_reduce=1 --iodepth=64 --bs=16k --fsync=1


texts:
I am trying to apply a role to a user and getting this error: SQL Error: ORA-00990: missing or invalid privilege. Here is where I created the role, which worked fine logged in as SYSTEM.

    CREATE ROLE LahmanDBDev;
    GRANT CREATE SESSION TO LahmanDBDev;
    GRANT CREATE TABLE TO LahmanDBDev;
    GRANT CREATE TABLESPACE TO LahmanDBDev;
    GRANT CREATE VIEW TO LahmanDBDev;
    GRANT CREATE ANY INDEX TO LahmanDBDev;
    GRANT CREATE SEQUENCE TO LahmanDBDev;
    GRANT CREATE SYNONYM TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.MASTER TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.BATTING TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.FIELDING TO LahmanDBDev;

Then I went on to create the user and grant the role to it, the second statement here where I grant the role is where I get the error:

    CREATE USER BudSelig
    IDENTIFIED BY BudSelig;

    GRANT ROLE LahmanDBDev
    TO BudSelig;


texts: There are a lot of database types. Can you list a list of the top classification of databases commonly found in schools and offices? Any help is appreciated. Hope to see compressed and summarized type as possible.


texts: detect what columns to select depending on a column value of the same table

texts:
I am trying to build a query which returns different columns of the table depending on a column of the same table. Say, a column represents a type of item.

This is my temporary solution, in which I just combine the results of multiple queries.

As you can see, I use "AS" keyword to rename the default column name.

SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 1
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 2
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 3
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [mail_domain]  AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 4
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 5
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 6
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [service_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 7


texts: What are the arguments in favor of using ELT process over ETL?

texts:
I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process.
What are the differences in the two approaches and in which situations would one be "better" than the other? It would be great if you could provide some examples.


texts: Given an arbitrary pair of origin & destination, how do I design a system that can effectively query all paths that cross the pair from a GPS dataset?

texts:
Data
I have a data set containing more than 8000 taxi drivers' GPS information, including time stamp, longitude, latitude, speed.

Objective
I intend to design a system which can query all taxi trajectories that cross any given pair of origin and destination and output these trajectories effectively.

Question
The queried origin-destination pair is random, lengths of trajectories (number of the pairs of longitude and latitude) do vary, not very structured, how could I achieve the objective?


texts: SQL Server 2016 HA Options for thousand databases

texts:
Currently we have a 4 node Active\Active\Active\Active Windows\SQL Server 2008r2(ENT Edition) cluster that we need to upgrade to Win2012r2\SQL Server 2016.

We have over a thousand databases, and growing, on the cluster (100mb -> 1tb) and development is rolling out an API that will require read only copies of each database.
Business ultimately wants to go to Azure (mid 2018 time frame)
Budget wants us to use STD Edition
Systems wants us to virtualize (Current Servers are physical 80
core\512gb RAM each)
DR is log shipping

I'm thinking of using Availability groups but for the read only secondary requirement it requires ENT Edition and the over a thousand DBs makes it a budget buster.

I was also thinking instead of AGs, using replication, but peer to peer is a monster to use with agile development. Individual publishing of a thousand databases is a lot to manage. Anyone have any thoughts?


texts: REBUILD - Clustered Index, TABLE, or both?

texts:
I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.

I have a very large table that we had to add a column to.  The clustered index is pretty highly fragmented, and I want to do an ALTER INDEX REBUILD to clean it up.

I also normally do an ALTER TABLE REBUILD when I change the columns since this cleans up any pointers or splits from that operation.

Do I need to do both since we are talking about a the clustered index, which is essentially the table?

My suspicion is the ALTER INDEX REBUILD on the clustered will not update everything that the ALTER TABLE will, but I also am afraid that the ALTER TABLE won't clean up the index fragmentation.


texts: Fail SSIS "Execute Process Task" if false is returned

texts:
I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned.

Here is the code

param ([string] $FolderLocation)

$FolderLocation = 'C:\Temp\IsFolderEmpty\'

if( (Get-ChildItem $FolderLocation | Measure-Object).Count -eq 0)
{
    #Folder Empty
    return $true

} Else {

    return $false

}

Any time I run this in SSIS, it always runs whether $true or $false is returned. I've tried creating a new variable and using it in the StandardOutputVariable but haven't had any luck.


texts: Number of data files in tempdb in Availability Group

texts:
We have configured AlwaysOn Availability Group with 3 node in Sync mode. The primary server got 16 core while the replicas have 10 core each. We have tempdb contention issue, just wondering if we can have different number of tempdb data files on nodes in Availability Group or do we have to have same number on each node. It is SQL Server 2016 Enterprise Edition.
Thanks in advance for help.


texts: How to get the AD account associated to a Proxy?

texts:
on the picture below, you can see, I have a sql server job called "Backup SSAS DBS_" that Run as:
PowerShell_Proxy

How can I find out the AD (active directory) account associated with that Proxy via T-SQL?

SO far I have this script below, but something is missing. Also, the AD account might not be a login or user in the current server.

USE [msdb]
GO

select
s.job_id
,s.name as [job_name]
,js.step_id
,js.step_name
,js.subsystem
,js.proxy_id
,js.command
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
LEFT OUTER JOIN sysproxylogin p ON js.proxy_id = p.proxy_id

WHERE S.NAME LIKE '%' + 'SSAS' + '%'

Using the query below I can get the SID used by the proxy.

select * from sysproxylogin


texts: We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to know is - Is there anything I can do now to make it compatible to 2012 also at the same time. Atleast those changes that are backward compatible from 2012 to 2008 etc.. The reason, I want to do this, is to minimize any effort in future (3 to four years from now) when we decide to move from 2008 to 2012. Anything at all I can do or need to be aware of?


texts: How to propagate a grandparent relationship throughout a tree?

texts:
I've got a MySQL table with columns id and parent, wherein each row has another row as a parent, except for some special root rows with parent=0. There are no cycles or broken relations.

In order to cache which root row each row is associated with, I decided to add an ancestor column. My current plan is to build the tree in some other programming language, find all ancestors recursively and then update the table.

However, I'm convinced that there's a clever way to do this in SQL, requiring not more queries than the depth of the three. Could someone help me design this?

There are ~1000 rows and the depth is ~5, so performance is not really an issue.


texts: Changing a column from NOT NULL to NULL - What's going on under the hood?

texts:
We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an INT). Just the nullability. The statement is as follows:

Alter Table dbo.Workflow Alter Column LineId Int NULL

The operation takes in excess of 10 before we stop it (we haven't even let it run to completion yet because it's a blocking operation and was taking too long). We'll probably copy the table to a dev server a test how long it actually takes. But, I'm curious if anyone knows what SQL Server is doing under the hood when converting from NOT NULL to NULL? Also, will affected indexes need to get rebuilt? The query plan generated doesn't indicate what's happening.

The table in question is clustered (not a heap).


texts: how to Update Value in local tabel when job Finished

texts:
I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs.

In other words, I want do something like status when the all jobs finished directly change this status .

I think that doing stored procedure like:

Update (Table Name )  Set ( Field ) = 1 )

But i don't know, how to execute the stored procedure, when the job finished.


texts: Distributed transaction and Read Committed Snapshot

texts: Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?