MySQL: How to decrease sleep process's time out? When I run 'show processlist;', then I get so many sleep processes.

I set wait_timeout and interactive_timeout to 60 in my.cnf. But, sleep process is not died when time 60 on processlist. I found that sleep process is died when time is 7900. What happend?? How can I decrease sleep process's time out?

If you need other information, I will give information as I possible.

UPDATE

I use root, read_only and deploy account on mysql. And I found root and read_only accounts run correct. Query -> Sleep -(60sec)-> release process.

But deploy account, using to connect between rails server and mysql, run wrong. Query -> Sleep -(7900sec)-> release. So, I think that deploy account or Rails is problem. However, I dont have any idea to fix this.

to check your idle process timeout you can connect to MySQL then execute the following command:

show variables like "%timeout%";

you need to check wait_timeout and interactive_timeout from the results based on those values you can execute the following commands from MySQL to set them:

set global wait_timeout=XXX; -- where XXX equal value less than the original one
set global interactive_timeout=XXX;  -- where XXX equal value less than the original one


No connection could be made because the target machine actively refused it I have created a PHP site using MySQL. It works well on localhost. However, when I moved it to a remote server, it stopped working.

The error shown is:

  "No connection could be made because the target machine actively refused it".

I am using MySQL Workbench. How do I go about resolving this error?

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'11.22.33.44' IDENTIFIED BY 'password';

What this means is that you lack privileges connecting to the mysql database from the remote host. What you basicly need to do is to grant those privileges with the above statement.


Is it possible to run an .exe file from a CLR function? Is it possible to run an .exe from CLR function with Sybase SQL Anywhere 12?

If yes then I thought about link this function and .exe with pipes, is it a good idea or do you have better ideas? If not then thanks for your time.

You can use xp_cmdshell for doing this.

As a little example from documentation:

CALL xp_cmdshell( 'dir > c:\\temp.txt' );

But take care: It's not possible to get e.g. STDOUT back into your database without output redirection.


Handling performance problems with jit in postgres 12 tl;dr: What is the best way to handle performance decrease caused by jit?

Background:

Lately I have migrated from postgres 11 to 12 and noticed, that some queries / procedures run considerably slower. I did some research and tested different configurations - it turned out, that the jit overhead exceeds its gains. I see two possible solutions:

Disable jit permanently for all queries by running set jit = off; once. This helps, but does not feel natural. (Maybe because of changing the default configuration, which I did not much as there was no such need. The defaults are sane and there is low probability they need to be changed.) This would also mean throwing away possible performance gains when running long queries.
Disabling jit at the beginning of the procedure and enabling it at the end. I have objections about the impact of changing the configuration on queries running in parallel. Of course this can harm performance of log queries, that would run alongside, but maybe this can cause other, possibly hard to detect bugs. Also this seems even less right than the first solution - changing the configuration as a part of the procedure.

The best way to handle this would be by fine-tuning the configuration, so that jit is not discarded in its entirety, but also configuration is not changed at runtime. Unfortunately I found little to no resource on how that would be possible (only parameters like jit_above_cost in the documentation that seem to be of little help).

Questions: What is the best way to solve this issue? Are there any criteria about when to disable jit except for running and measuring? Can the behavior be fine-tuned (for example jit disabled for specific queries)?

Edit: I'm attaching a plan with and without jit. The plans are too big to post here.

In my opinion changing the default of jit to 'on' in v12 was a mistake, so based on that opinion turning it back off again seems pretty natural to me.  I would have objected to the change if I had been paying more attention at the time (which doesn't mean I would have prevailed, of course).  The change seems to harm at least as many people as it helps.  And the people who were looking forward to the feature could surely be counted on to go change the default.

  The defaults are sane and there is low probability they need to be changed

I think you have far too much respect for the defaults in general. They are basically set for the minimal credible machine size.  Even the settings that were well chosen in general may not be well chosen for you.

  This would also mean throwing away possible performance gains when running long queries.

Do you have example of such a query? Generally by the point the query is long-running enough that jit would make sense, it also starts touching enough data that it is no longer CPU bound and so that using jit or not is relatively unimportant (or it is missing an index, which the fixing of is far more important than jit). This is not universally true of course, and if you have an example you could then possibly use that to target the setting of jit_above_cost.  (One problem with the jit_above* things is that they are based on total cost estimates, which are generally dominated by IO cost estimates.  But choosing to use JIT or not based on estimated IO costs doesn't make sense.  But the infrastructure isn't in place to separate out those types of cost estimates.)


Does PostgreSQL PDPG support Yakkety (Ubuntu 16.10)? The official repository does not have Yakkety (16.10 packages). It seems it stops at Xenial.

PostgreSQL's PDPG only creates separate packages for LTS releases, however it seems to suggest that it should work in the FAQ

  I am using a non-LTS release of Ubuntu
  Non-LTS releases of Ubuntu are only added to the repository if the packages from the latest LTS release are incompatible with the release in question. Using the latest LTS release instead generally works, unless library dependencies cannot be fulfilled, in which case we will provide updated packages for the non-LTS release.

I'm a little hesitant to spring for generally works, but there you have it.

So do not run the suggested command,

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Instead, run

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Warning, I wasn't not able to get this to work... Installing postgis failed.

 postgresql-9.6-postgis-2.3 : Depends: libgdal1i (>= 1.9.0) but it is not installable
                              Recommends: postgresql-contrib-9.6 but it is not going to be installed

Ubuntu 16.10 does not ship with libgdal1i, it ships with libgdal20 and libgdal-dev which uses 2.1.1. Bug filed here


How to check if a trigger fires on INSERT, UPDATE or DELETE statements? How do I determine whether a trigger is targeting INSERT, UPDATE or DELETE statements?

I've checked in sys.triggers and I can't see anything related to what operation the trigger fires on.

The sys.triggers table does not expose this data, instead we have two options:

OBJECTPROPERTY

Using the OBJECTPROPERTY metadata function we can return the action as a property as below:

OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger')
OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger')
OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger')

This will return a simple bit value indicating whether the trigger fires on that particular action.

sys.trigger_events

As per the docs this contains a row per event for which a trigger fires. Specifically the columns we're interested in are type and type_desc.

type is a foreign key to the sys.trigger_event_types table - type_desc seems to contain the type_name column from the sys.trigger_event_types table.

This data can be returned using the following script:

SELECT
    [o].[name]
   ,[t].[type_desc]
FROM
    [sys].[trigger_events] AS [t]
    INNER JOIN [sys].[objects] AS [o] ON
        [o].[object_id] = [t].[object_id]
WHERE
    [o].[name] = 'TriggerNameHere';


Single disk for Data, System, User, Temp and Backup when installing Fail Over Cluster? I have a Windows cluster with two nodes. I am trying to make Fail Over Cluster using SQL Server 2012.

On both nodes there is 2 TB storage available which I can access as:

C:\Storage_For_Cluster\Volume1

So basically a 2 TB SAN is mapped on both nodes as above.

My questions is, can I make Fail Over Cluster with one disk only and all data will be saved in it? Or it is better to use a separate drive for each. If you take a look at the screenshot below, you will get an idea as to what I am talking about. As you can see we are using two drives Z and X for different options.

Since in this case I only have one drive, can I use this for all these options?

Secondly MSDTC is not installed yet (which I think is also required to make Fail Over Cluster) so I wanted to know shall I install MSDTC too on same drive as mentioned above?

MSDTC has to be installed on a separate drive while rest can be installed on another drive.

EDIT

MSDTC is needed on separate drive because once you start installing SQL Server, the drive where MSDTC is installed won't be selectable and you will see message on screen something like "Cluster Disk 2 is already in use by MSDTC".

Secondly, according to folloing article MSDTC is needed when you are using SSIS, which I am actually using.
http://technet.microsoft.com/en-en/library/ms189910.aspx


How to load .dbf to run select queries? I had asked for database(Oracle 11g) back-up files (for a particular date)from a third party vendor. The files I received  were with the following extensions :   .dbf  , .svd ,    .arc ,   .ctl  (PFA showing the files).
Now my concern is to load the data from this file in ‘Toad for Oracle , Version 10.5.1.3‘ software – I usually use the same for  working on this particular database .
The purpose of loading this database is to run some select queries(along with lot of joins) so as to extract the data from the files which can be shared  with the requestor .
        I am not very sure which file will have the complete data as there are files with various extensions . Also , not sure if ‘Toad for Oracle’ has the facility to open /load dbf or other aforementioned files . I don’t have DBA rights for this database as it is maintained by 3rd party vendor .  Please let us know if Toad can be used for the same or a new software   needs to be installed for this purpose . Also , what steps are required to be followed to do the above mentioned task .I do have the details related to schema name , username/schema and password to the database !Quick help on this is  much appreciated

Toad cannot directly read Oracle data files, no.  You would need to recover the database on a local server.  Once you do that, you could connect to Oracle database on the local server from Toad and run whatever queries you'd like.

Assuming that what you've been given is a consistent cold backup of your database, you'd need, at a minimum, exactly the same version of Oracle (patchsets included) on exactly the same operating system that the third party vendor uses.  Assuming that the vendor is vaguely competent about applying patches, that will likely require that you have a My Oracle Support account which would require that your organization has appropriate Oracle licenses.  How you do that will depend on the operating system your Oracle server will be using.

Since it sounds like you are not a DBA and you're not particularly familiar with Oracle, assuming there is a reason that you can't simply access the existing database to run whatever queries you need to run, I would tend to suspect that you would be much better off requesting an export of the database (or potentially the tables you're interested in if you really care about a subset of the data) rather than a backup.  Toad can't read an export directly either but it is a lot easier to import and export file into a database you create on your local server without worrying about matching the version and operating system that the vendor uses or going through the effort of doing a proper database restore.


SQL INSERT and UPDATE queries run far to slow I have two queries that run far too slow and I have no idea why.

Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac).

I have tested the queries with MAMP MySQL (InnoDB).

UPDATE foo_new as new
JOIN foo_active as active
ON active.id_3=new.id_3
SET active.norf=new.norf,
    active.time_left=new.time_left

-

INSERT INTO foo_active (id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved)
SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
FROM foo_new new
WHERE NOT EXISTS (
    SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
    FROM foo_active active
    WHERE new.id_c = active.id_c
)

Both tables have the same structure:

Database Indexes are a major benefit to performance of queries.  I work almost exclusively with Microsoft SQL Server, but the principles apply on pretty much all flavors of SQL servers.

Because the indexes are smaller than the overall table and have the needed value to be searched in a sorted order, creating indexes (to a reasonable degree) is the quickest way to speed up queries.

You can overdo it, of course, since too many indexes can drag down performance.


"Corrupted key" error when creating/updating SQL Server 2012 credential for proxy account Following a restart of a SQL Server 2012 SP3 instance caused by a reboot of the unresponsive host WINTEL server, I have a SQL Agent Log Shipping Copy job (which uses a proxy account to access the NAS file-share where the log backups reside) that is failing with this error:

  Error authenticating proxy MyDomain\MyProxyAccount, system error: The user name or password is incorrect.

I have attempted to reapply the correct password to the proxy’s Credential and to recreate a new Credential and both fail with this error:

  A key required by this operation appears to be corrupted. (Microsoft SQL Server, Error: 15507)

I’ve read an MSDN page that says:

  SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. The Service Master Key is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key.

I’m uncertain how to proceed. Do I have a problem with the Service Master Key? If so, can anyone suggest how to resolve this issue or direct me to further reading online?

Update: I've found entries in the SQL Server log (written when the instance was restarted following the host WINTEL server reboot) that further suggest that the issue lies with the Service Master Key:

  Service Master Key could not be decrypted using one of its encryptions.
  An error occurred during Service Master Key initialization.

I'd still welcome any suggestions on how to fix this...

I am going to attempt to answer my own question (as my experience may help others). Firstly, regarding the issue I experienced, I found this blog helpful (as were the comments from Sean Gallardy above).

As I had no Service Master Key (SMK) backup to restore from, my intention was to regenerate a new SMK using the ALTER SERVICE MASTER KEY REGENERATE with the FORCE option (causing loss of encrypted entities). I was prepared to have to recreate my credential secrets and Linked Server passwords. This instance had no Database Master Keys but, had it done so, I could have opened them using their password and regenerated them by the SMK (thus avoiding data loss).

However, I decided first to see if a simple restart of the SQL Server instance would resolve the issue - and it did! Upon restart, the Service Master Key verified successfully (and I confirmed this by creating a new test credential successfully).

My best guess is that whatever issue prevented the SMK verification previously (causing the "Service Master Key could not be decrypted using one of its encryptions" error log entry) had disappeared. I know that the SMK always has two encryptions: the machine key encryption and the service account encryption (and these provide an either-or decryption method in the case of cluster failover or service account change).

As the host WINTEL server was having issues, I imagine that invalidated the machine key encryption. I can't imagine what might have been invalidating the service account encryption (that account did not change) but whatever it was, it seemed to be a temporary problem. So my best guess is that, upon restart, the SMK was able to self-verify using the service account encryption (and, at that point, create a new machine key encryption).


How to update/insert/inject nodes into XML I have an application which stores optional Site information in an XML field.

Sample table:

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteName] [nvarchar](80) NULL,
    [SiteInfo] [xml] NULL );

INSERT INTO [dbo].[Sites] ([SiteName]) VALUES  ('TestSite1') ;

If no attributes apply the XML field is NULL. If any of those attributes do apply then the application inserts an XML document and relevant nodes.

A valid example looks like this:

<SiteInfo>
  <BoreID>ABC123</BoreID>
  <ConsentCompliance>true</ConsentCompliance>
</SiteInfo>

I need to migrate some data from another system, and so I'm trying to insert data into this field/XML. So far my attempts have resulted in singleton nodes which the app doesn't like.

Using some examples i found in the web I've tried a the following:

update [dbo].[Sites]
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo)')
where Siteid = 1

Which throws the following error:

  Msg 2226, Level 16, State 1, Line 1 XQuery
  [dbo.Sites.SiteInfo.modify()]: The target of 'insert' must be a single
  node, found 'element(SiteInfo,xdt:untyped) *'

Questions:

If the field is NULL, Do I need to update the field first to insert a base for the xml i.e  before I can insert to it?
Whats the name of an XML node that doesn't use an opening and closing pair. For example: <BoreID>ABC123</BoreID> vs a single side <BoreID=ABC123/>
Are there other/better methods of inserting nodes into XML using SQL?

Cheers

Pete

Answers:

Yes  update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'
Shorthand vs Long
No

You just need to specify the first SiteInfo node using [1]

update [dbo].[Sites]
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])')
where Siteid = 1


Repating COUNT in WhereIN condition I have this query and works. But It seems ugly to me that it needs some optimization since I repeat the same actions over and over again.
There must be a better way to merge all of these

SELECT *
FROM
  (SELECT COUNT(*) AS a
   FROM order_item_histories
   WHERE order_status_id IN (5, 4, 15)) AS e,
  (SELECT COUNT(*) AS b
   FROM order_item_histories
   WHERE order_status_id IN (6)) AS f,
  (SELECT COUNT(*) AS c
   FROM order_item_histories
   WHERE order_status_id IN (4, 10, 9, 12, 7)) AS g,
  (SELECT COUNT(*) AS d
   FROM order_item_histories
   WHERE order_status_id IN (11, 16, 17, 13)) AS h

Use conditional aggregation:

select count(*) filter (where order_status_id in (5,4,15)) as a,
       count(*) filter (where order_status_id in (6)) as b,
       count(*) filter (where order_status_id in (4,10,9,12,7)) as c,
       count(*) filter (where order_status_id in (11,16,17,13)) as d
from order_item_histories;


When is data moved during an Oracle partition split? I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do:

alter table SCHEMA.DATATABLE
   split partition MAXVAL_PART
   at (TO_DATE(' 2013-04-01 00:00:00',
               'SYYYY-MM-DD HH24:MI:SS',
               'NLS_CALENDAR=GREGORIAN'))
   into (partition PARTFOREXISTINGROWS
        tablespace EXISTINGMAXVALTABLESPACE,
         partition MAXVAL_PART
        tablespace NEWMAXVALTABLESPACE);

The problem is that my current MAXVAL_PART has 320 GB of data in it, and I don't want to physically move this data on the disk. If I cut off the partition at 2013-04-01, then there will be no data in the new partition, but I'm getting conflicting information about whether this will still necessitate a move of all the data. Ideally, Oracle sees that my new MAXVAL partition will be empty, defines it in the new table space, and I'm all done.

Will this command move the data that's already on the disk, or will it leave it in place and just create a new partition?

This article says Oracle will detect the empty partition and do a fast split
http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm

But I see in a number of places that a split means all data gets moved to new storage. Which is it?

It won't move the data.

Test case:

SQL> CREATE TABLESPACE myts1 DATAFILE '/u01/app/oracle/oradata/ORA112/myts1_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLESPACE mytsmax DATAFILE '/u01/app/oracle/oradata/ORA112/mytsmax_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLE mytable (id NUMBER, dt DATE)
PARTITION BY RANGE (dt)
  (PARTITION mytablep1 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep2 VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep3 VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep4 VALUES LESS THAN (MAXVALUE) TABLESPACE myts1
  );

Table created.

I inserted some data. The results:

SQL> select dt, count(*)
  2  from mytable
  3  group by dt;

DT      COUNT(*)
--------- ----------
11-NOV-12     262272
11-JAN-13     262272
11-FEB-13     262272
11-MAR-13     262272

Now, the best way of working out if a row has moved is to look at its rowid. The rowid for a given row is based on the location of the row in a datafile and block. Therefore, if the rowid changes it means the row has moved! Documentation link.

So, create a table to hold the rowids of the existing rows in the table, and insert them:

SQL> create table myrowids ( r rowid );

Table created.

SQL> insert into myrowids ( select rowid from mytable );

1049088 rows created.

SQL> commit;

Perform the split:

SQL> ALTER TABLE mytable
SPLIT PARTITION mytablep4
AT ( TO_DATE('2013-04-01', 'YYYY-MM-DD') )
INTO ( PARTITION mytablep4 TABLESPACE myts1, PARTITION mytablep5 TABLESPACE mytsmax );

Table altered.

SQL>

Check that the rows haven't moved (could have used an EXISTS instead):

SQL> select count(*) from myrowids where r not in ( select rowid from mytable );

  COUNT(*)
----------
     0

SQL>

They haven't!


What is exactly a uniform server in a galera cluster setup? In many manuals about performance tuning and avoid Galera's MySQL cluster limitations they write the below suggestion.

  It is possible for a slow/overloaded master node to affect performance
  of the Galera Replication cluster, therefore it is recommended to have
  uniform servers across the cluster.

I cannot find any information about what a uniform server is? Has anybody an idea?

The word "uniform" is being used to describe the fact that each server should have similar or identical resources, in terms of cpu, memory, disk (seek time, I/O bandwidth), etc.

Since a single node can slow down the entire cluster, the slowest node determines the overall capacity of the cluster... so using servers whose performance capabilities are not "uniform" (closely comparable) will mean that resources on the more powerful machines will go underutilized, while the less powerful machines slow down the cluster.


Cross-Continent SQL High-Latency Data Synchronization How would one go about synchronizing a MySQL database cross-continent, while keeping the distributed application using the DB running fast (<0.2s page loads) ?

My database contains about 2.5GB of data, having almost all 200 tables indexed by auto_increment int key, so some kind of MASTER-MASTER replication seems not quite possible, as duplicate keys might occur.

The hardware my project runs at are located in 2 datacenters in US-West and EU-West (130ms ping latency between each other)

I've thought about using a Master SQL server in EU-West, and having a slave server in both regions, and having the application servers connect directly to the master server for insert/update queries only, and connecting to the slave servers for select queries.

The thing is that when having to execute a few insert/update queries, this may cause really slow page loads for users connected to US-West. Even when running the queries async can cause a slow user experience.

Is there a different way how I can guarantee that users in neither region experience slow page loads due to having to write to a high-distance SQL server, while keeping data synchronized between the 2 regions fast, without having to worry about duplicate keys and synchronization errors ?

Duplicate keys with AUTO_INCREMENT are not "possible".  See auto_increment_increment, which must be set to 2.

In general M-M is problematic for a number of reasons, which I will not go into, especially if you write to both of them.  You are better off writing to only one of them, leaving the other as a hot failover server.

For pages that only need read access, have Slaves in both datacenters.

Have client machines in both both datacenters; have them hit the local Slave for reading (if it is not down).

But beware of the "critical read" problem.

Plan A:  Change user expectations so that they understand that writing may be slow.

Plan B:  Use NDB Cluster with its "eventual consistency" model of replication.  Caveat: There are a lot of differences between InnoDB and NDB.

A technique that may help:  If you need to send multiple queries to a 'remote' server, package them in a Stored Procedure so that there is only one cross-continent hop.  (This, of course, is not always possible.)


How does PostgreSQL's citext type affect string comparisons made using LOWER()? The impetus for my question is that I had hoped that PostgreSQL would behave consistently when selecting from citext columns, regardless of whether or not the string to be matched is wrapped in one or more instances of lower() (any such wrapping is beyond my control). That appears not to be the case. (Of course, it is entirely possible that my tests are invalid or I am misunderstanding fundamental concepts.)

Steps to Reproduce Testing Scenario

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (id int, email citext);
INSERT INTO users(id, email) VALUES
  (1, 'USER@example.com');

Tests

As expected when using the citext type, the lowercase variant yields a result:

# select * from users where email = 'user@example.com';
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

Changing the = operator to like yields a result:

select * from users where email like lower('user@example.com');
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

As does the "inverse":

# select * from users where lower(email) = 'user@example.com';
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

As does wrapping both values in lower():

# select * from users where lower(email) = lower('user@example.com');
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

My Question

Why then does the following query not return a result in this instance?

# select * from users where email = lower('user@example.com');
 id | email
----+-------
(0 rows)

The manual says of the citext type:

  Essentially, it internally calls lower when comparing values.

The operative word seems to be "essentially"; this statement implies the following, which does yield a result:

select * from users where lower(email) = lower(lower('user@example.com'));
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

Might this be related to the following caveat in the Limitations section of the above-cited document?

  citext's case-folding behavior depends on the LC_CTYPE setting of your database.

# SHOW LC_CTYPE;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

Any explanation in this regard is much appreciated.

tldr; when comparing case insensitive and sensitive things for equality, you have to be explicit. text is explicitly case-sensitive; citext is explicitly case-insensitive. You should provide a cast for both sides and be explicit

A few things about lower()

lower() is typed
When it's argument are text, it always returns text

A few other points

When you do a comparison with a literal, the type isn't known (it's explicitly unknown internally).
Operators are functions.
Functions coerce the types in PostgreSQL in runtime.

In this case types are as follows, with description

-- text = unknown
-- unknown promoted to text, this has nothing to do with citext
lower(email) = 'user@example.com';

-- text = text
-- this has nothing to do with citext
lower(email) = lower('user@example.com');

-- text = text
-- this has nothing to do with citext
lower(email) = lower(lower('user@example.com'));

-- citext LIKE text
-- LIKE is smart `operator ~~(citext,text)` via `texticlike`
-- WORKS
email like lower('user@example.com');

-- citext = unknown
-- unknown promoted to citext, there is an `operator =(citext,citext)`
-- WORKS
email = 'user@example.com';

-- citext = text
-- citext promoted to text, there is no `operator =(citext,text)`
-- FAILS
email = lower('user@example.com');

In summary, there is an operator =(citext,citext). So you can

email = lower('user@example.com')::citext;

If you want, or you can define your own operator that sets = to the case insensitive route rather than the case sensitive route. I find that to be horrible practice though, I'll always cast.


Advantages of POINT over DOUBLE for distance queries? I'm about to start an application that will need a simple distance query to order results. After some search I've discovered some functions that seems to do this work pretty easily without needing PostGIS, what seems to be too much for my task.

My question is: what would be better from a performance point of view, using a POINT datatype to store lat/long or two DOUBLE fields, separately? Would they have any difference?

Bonus question: is the approach from that link a nice idea indeed?

A point consist of two double precision values. You store the same information, same size on disk and in RAM. But with a slightly different set of constraints.

Two separate columns can be NULL or not independently. A point can only be NULL as a whole:

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

You can use geometric functions and operators for points. But you can easily construct a point from two numbers on the fly: point(x, y). In reverse, you can easily access coordinates of a point as double precision numbers: p[0], p[1] (0-based index!)

It's basically a matter of taste and style while you don't use the more sophisticated types geometry or geography provided by PostGis.


speeding up a query on MySql I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.

create table t2
select field_1,
       sum(ifnull(field_2,0)) as field_2,
       sum(ifnull(field_3,0)) as field_3,
       sum(ifnull(field_4,0)) as field_4
from t1
group by field1;

The data type of all the columns is varchar(200).

This query is not able to produce the result even after running for more than 1 day. Any suggestions on getting the results quickly will be helpful.

Here's what I'd do:

Use EXPLAIN on your SELECT to reveal which indexes it's using.
Tune your query and repeat step 1 until satisfied.
Create your new table with the desired structure.
Use INSERT ... INTO new table SELECT FROM ... old table replacing the select part with the SELECT query you tuned in step 2.


SQL Server Partial Only Database Move My company purchased part of another company. Now they have a large SQL Server 20TB database that is supposed to be moved to our data center. The problem is that only part of the database is being moved because the rest was not part of the merger.

For instance, part numbers and stuff are moving but only parts that are part of the merger deal. The rest is still considered the other companies property.

Is this possible?

I am a new Jr DBA and I mostly focus on Oracle not SQL Server.

If the objects you want to move are part of a separate filegroup, then you can do a filegroup backup and restore. This is a little tricky - with SQL Server, you can do a piecemeal restore of the primary filegroup, plus other filegroups, and the objects are then online. However, this won't work if the database wasn't designed for this from the start (by separating objects into filegroups.)

If the objects are relatively small, and if all objects are in a filegroup other than the primary, AND if you were allowed to make changes to the database, then you could create a new filegroup, move the objects into it, and then restore just that filegroup. However, you still have to restore the primary filegroup first, so this only works if there's not much in the primary filegroup.

If those aren't the case, you could set up a new database in the other company's data center, and copy the objects in that you'd like to transfer over. You could do this with simple insert statements, or by setting up replication into the new database.

None of what I've said above is easy - but hopefully it plants a few seeds on other ways you could pull it off.


Is there an automatic way for a secondary member to promote to primary member in a replica set? Here is my setup:
Node 1 = 10.1.0.1 (Primary) - priority 1
Node 2 = 10.1.0.2 (Secondary) - priority 1
Node 3 = 10.1.0.3 (Secondary) - priority 0

In the event of Node 1 failure, Node 2 will be set to Primary of the mongo replica set.
Once Node 1 is already accessible, is there a way to automatically promote it to Primary and Node 2 will be set to Secondary again once Node 1 catches up on the replica set?

The main factor here is the Replica Set Elections.
Replica sets use elections to determine which set member will become primary. Replica sets can trigger an election in response to a variety of events, such as:

Adding a new node to the replica set,
Initiating a replica set,
Performing replica set maintenance using methods such as
rs.stepDown() or rs.reconfig(), and
The secondary members losing connectivity to the primary for more
than the configured timeout (10 seconds by default).

I think this is what you are looking for:
rs.stepDown()
instructs the primary of the replica set to become a secondary. After the primary steps down, eligible secondaries will hold an election for a new primary.
Another option would be that you can force a replica set member to become primary by giving it a higher members[n].priority value than any other member in the set. Note that a majority of the configured members of a replica set must be available for a replica set to reconfigure or elect a primary.


Oracle: Interval partitioning and dependant tables I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to another 80M records between them. This database is basically a log of data so the data going in is written once and only read after that.

The admims want to partition the data per month because they want to quickly remove old data after two year and avoid having to rebuild all the indexes. So their intent to to remove old partitions.

After reading a lot, my idea was to do interval partitioning as per many examples I found on the net, however I then found that the other two tables cannot then employ partition by reference. Why oracle does not allow this, no-one here knows.

So my question is what, in your experience, would you do to partition the data? given it's is spread across the 3 tables. And is this the best technique for handing a rolling removal of old data like this?

You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:

  The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.

Range partitioning involves a bit more maintenance because you have to create the partitions  yourself. However, once the partitions are created, range and interval work similarly.


oracle distinct doing sort I'm getting poor performance from DISTINCT.  The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right.  I would expect some kind of HASH aggregation to produce much better result.  Is there a hint to tell oracle to use HASH for DISTINCT rather than sort?
I've used /*+ USE_HASH_AGGREGATION */ in similar situations, but it is not working for DISTINCT.

So this is my original query:

SELECT
count(distinct userid) n, col
FROM users
GROUP BY col;

users has 30M rows, each userid is there 12 times.  This query takes 70 seconds.

Now we rewrite it as

SELECT
count(userid) n, col
FROM
(SELECT distinct userid, col FROM users)
GROUP BY col

And it takes 40 seconds.
Now add the hint to do hash instead of sort:

SELECT
count(userid) n, col
FROM
(SELECT /*+ USE_HASH_AGGREGATION */ distinct userid, col FROM users)
GROUP BY col

and it takes 10 seconds.

If somebody can explain to me why this is happening or how I can beat the first simple query into working as good as the 3rd one, that would be fantastic.
The reason I care about query simplicity is because these queries are actually generated.

Plans:
1) Slow:

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |      5 |00:01:12.01 |     283K|    292K|       |       |      |     |
|   1 |  SORT GROUP BY     |               |      1 |      5 |      5 |00:01:12.01 |     283K|    292K|   194M|   448K|  172M (0)|   73728 |
|   2 |   TABLE ACCESS FULL| USERS |      1 |     29M|     29M|00:00:08.17 |     283K|    283K|       |       |      |     |

2) Fast

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      5 |00:00:13.09 |     283K|    283K|   |   |      |
|   1 |  SORT GROUP BY       |               |      1 |      5 |      5 |00:00:13.09 |     283K|    283K|  3072 |  3072 | 2048  (0)|
|   2 |   VIEW               |               |      1 |   8647K|   2445K|00:00:13.16 |     283K|    283K|   |   |      |
|   3 |    HASH UNIQUE       |               |      1 |   8647K|   2445K|00:00:12.57 |     283K|    283K|   113M|    10M|  216M (0)|
|   4 |     TABLE ACCESS FULL| USERS         |      1 |     29M|     29M|00:00:07.68 |     283K|    283K|   |   |      |
--------------------------------------------------------------------------------------------------------------------------------------------

Can you add indexes? I would first try adding an index on (col, userid).

10 seconds looks too much for a 30M rows table.


MySQL update tables in batches I want to update my MySQL table with batches.

The table has 10000000+ records, so I want to update first 1000 rows, once it is done then update 1001 to 2000 rows. I need to make it as a loop.

I use this form for delete obsolete data monthly day by day, but for update it will work also:

adopted for UPDATE procedure example:

DELIMITER $$

CREATE DEFINER = `root`@`localhost` PROCEDURE `geo_opt_2017`.`udp_batch_update`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER

BEGIN

SET @event_id = 1;

WHILE EXISTS (SELECT event_id FROM geo_opt_2017.event WHERE event_id > @event_id AND event_id <= @event_id + 1000) DO

    UPDATE geo_opt_2017.event SET event_lat_int = event_lat_int * 1 WHERE event_id > @event_id AND event_id <= @event_id + 1000 ;

    SET @event_id = @event_id + 1000;

END WHILE;

END $$
DELIMITER ;

SET portion - is dummy, just for illustration, You need replace it for real procedure logic

The logic will proper work with many numeric or dates - simple increment by  numeric or date time interval.

with tables where PK - combined from different columns without any visible logic, like telecom tables - (prefix, context, priority) it also possible, but little more complicated.


Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space? Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered indexes, implementing row or page compression, etc.

However, there are a few cases where I was able to reclaim even more space by executing DBCC CLEANTABLE. With hundreds of databases in my environment, it's not possible to know what users do in each one and it's completely acceptable that there will be changes involving the dropping of fixed length columns. I've typically found these opportunities by looking at row counts vs. page counts in some object space utilization scripts I've written. I'd like to take this a step further by attempting to automate the detection of these sorts of scenarios.

What I'd like to know is if anyone out there is actively monitoring for these sorts of opportunities and if so, what do you look for specifically?

My thoughts were to write something along the lines of gathering the max and min size of a row, the number of rows in the table, the number of pages allocated, and the number of pages used, then doing some basic math to log results that are well outside what would be "expected".

The solution I'd think of for this problem is to run weekly a job that will run sp_spaceused for all tables in a database and save this data in a table. If there are differences in size for each table greater than..let's say..10%, I would run the dbcc cleantable.

My code to loop through table sizes looks like that:

if OBJECT_ID ('tempdb.dbo.#temp') is not null
    drop table #temp;

if OBJECT_ID ('dbo.BigTables') is not null
    drop table dbo.BigTables;
go

CREATE TABLE [dbo].[BigTables] (
    [table_name] [sysname] NOT NULL,
    [row_count] [int] NULL,
    [col_count] [int] NULL,
    [data_size] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [DBName] [nvarchar](128) NULL
);
GO

CREATE TABLE #temp (
    table_name sysname ,
    row_count int,
    reserved_size varchar(50),
    data_size varchar(50),
    index_size varchar(50),
    unused_size varchar(50)
);
go

INSERT     #temp
EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables
SELECT     a.table_name,
           a.row_count,
           count(*) as col_count,
           a.data_size,
           getdate() as [Date],
    'MY DB' as DBName
FROM       #temp a
INNER JOIN information_schema.columns b
           ON a.table_name collate database_default
                = b.table_name collate database_default
GROUP BY   a.table_name, a.row_count, a.data_size
ORDER BY   CAST(Replace(a.data_size, ' KB', '') as integer) desc

DROP TABLE #temp;

Select * from dbo.BigTables;

Now you only need to build the logic that will verify what would be the size change through the week and schedule it.


Getting aggregate sub-values in aggregate query I have a query that need to show aggregated values in an already aggregated outer query.

Consider a query that fetches total count of accounts being credited on a certain day.

I would like to display

total number orders
Total orders that are "PENDING"
Total orders that are "OPEN"

Here is the query that seems to make sense but as resultset show below, I am obviously not using the right approach. The "OPEN" column displays the entire total, the "PENDING" column stays NULL.

I feel I may be using the wrong approach, what are the options to have this work?

SELECT s.created, COUNT(s.id) 'total_accounts_credited', SUM(s.withdrawal) 'total_amount_credited',
(select count(s.id) WHERE s.status_id = 'OPEN')  total_open_credited,
(select count(s.id) WHERE s.status_id = 'PENDING') total_pending_credited
  FROM statements s
WHERE s.status_id in ('OPEN', 'PENDING')
GROUP BY
  YEAR(s.created),
  MONTH(s.created),
  DAY(s.created)
  ORDER BY s.created DESC
;

Results:

+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| created             | total_accounts_credited | total_amount_credited | total_open_credited | total_pending_credited |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| 2019-01-19 00:00:00 |                   18050 |              20813.18 |               18050 |                 NULL |
| 2019-01-12 00:00:00 |                   18135 |              24768.43 |               18135 |                 NULL |
| 2019-01-10 09:00:27 |                      80 |               1497.75 |                  80 |                 NULL |
| 2019-01-09 09:20:55 |                      51 |                933.50 |                  51 |                 NULL |
| 2019-01-08 16:45:14 |                      10 |                187.50 |                  10 |                 NULL |
| 2019-01-05 18:21:00 |                   17588 |              16968.49 |               17588 |                 NULL |
| 2018-12-29 00:00:00 |                   17893 |              25404.18 |               17893 |                 NULL |
| 2018-12-28 15:23:04 |                       1 |                 35.00 |                   1 |                 NULL |
| 2018-12-22 00:00:00 |                   17353 |              17048.18 |               17353 |                 NULL |
| 2018-12-15 00:00:00 |                   16893 |              10181.34 |               16893 |                 NULL |
| 2018-12-08 00:00:00 |                   16220 |              99547.09 |               16220 |                 NULL |
| 2018-12-01 00:00:00 |                   15476 |              87699.59 |               15476 |                 NULL |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+

SELECT  ...,
        SUM(s.status_id = 'OPEN') AS total_open_credited,
        ...
    FROM ...
    WHERE ...
    GROUP BY DATE(s.created)   -- a simplification
    ORDER BY ...

Explanation:  s.status_id = 'OPEN' is a boolean expression that evaluates to TRUE or FALSE.  TRUE is 1; FALSE is 0.  Then SUM correctly tallies what you asked for.


SQL - Many-to-Many relationship with same table and with relationship constraint I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns:

id (serial, pk)
productName (nvarchar(50))
productDescription (ntext)
productPrice (decimal(10,2))
sellerId (int, fk to Seller table)

A product may be the same across sellers, but the productName, productDescription and productPrice can vary per seller.

For example, consider the product TI-89. Seller A may have the the following information for the product:

productName = TI-89 Graphing Calc
productDescription = A graphing calculator that...
productPrice 65.12

Seller B may have the the following information for the product:

productName = Texas Instrument's 89 Calculator
productDescription = Feature graphing capabilities...
productPrice 66.50

Admin users will be required to identify that products are the same across various sellers.

I need a way to capture this information (i.e. products are the same across sellers). I could create another table called SellerProductMapper as follows:

sellerProductId1 (int, pk, fk to SellerProdcut table)
sellerProductId2 (int, pk, fk to SellerProdcut table)

The problem with this approach is that it permits sellerProductId1 and sellerProductId2 to be from the same seller for a given row. That should not be allowed.

How can I capture this many-to-many relationship while enforcing this constraint?

If I understand your scenario description correctly, you want to manage two different facts, so you should have (a) one table to store product data that is independent of sellers and (b) another table to retain product information that comes into play in the context of each relationship between products and their corresponding sellers.
1. Logical model
I have depicted such approach in the logical IDEF1X[1] data model shown in Figure 1 and you can download it from Dropbox as a PDF.

1.1 Product
As you can see in the referred model, you may use the Product entity type (or table at the implementation level) to store the default (or base) values of the product attributes. It can be useful to think about this entity as some kind of catalogue. Here is where you can keep the product data that does not depend on sellers.
I have denoted the Product PK as ProductNumber for expository porposes but, possibly, the use of some sort of ProductCode or another similar term might be more suitable or advantageous in your business domain.
1.2 ProductSeller
Then, I have set up a PRIMARY KEY (PK) in the ProductSeller associative entity which is composed of SellerId and ProductNumber, and these attributes are, in turn, FOREIGN KEY references to Party.PartyId and Product.ProductNumber, respectively. This is the place where you could store product names, descriptions and prices as defined by every specific seller.
1.3 Party
I decided to include the Party entity in case you have to store sellers that are either an Organization or a Person. These three entities are encompassed in a exclusive Supertype-Subtype relationship, so this post could be pertinent.
1.4 Implementation considerations
It is important to note that I indicated some attributes as ALTERNATE KEYS (AK plus its fitting number), since this implies that, when they become table columns, they should be set with a UNIQUE CONSTRAINT or UNIQUE INDEX.
As in any relational database development, you should seriously consider using ACID Transactions in order to guard your data integrity and consistency.
2. Simplified logical model
If in your case it is only possible for an Organization to become a Seller, then you can employ a structure like the one I propose in the logical model presented in Figure 2, and it is also downloadable from Dropbox as a PDF.

With this model, you just have to migrate[2] the OrganizationId PK from Organization to SellerProduct, and then assign it a role name[3] like SellerId in order to make it more meaningful or, maybe, leave it as OrganizationId if you feel more comfortable. After that, you migrate the Product.ProductNumber to complete the composition of the SellerProduct PK.
3. Data entry process
Having discussed the most relevant parts of the two suggested structures, I esteem opportune addressing the process that should take place when the data entry is being carried out. In this respect —with the aid of an application program that communicates with your database— your properly authorized users can:

Inspect carefully the product information detailed by one certain seller.
Compare it with the rows already contained in the Product table.
If they define that such product instance has not been entered yet and therefore it is not provided by any seller, then they (1.1) INSERT a new Product row with the default or base values and also (1.2) a new row in the ProductSeller table including the suitable SellerId (drawn from Party.PartyId or Organization.OrganizationId) and the ProductNumber just created along with the remaining column values holding the product data as described by that seller in particular.
If, on the contrary, they determine that the product under inspection is already stored in the catalogue and, perhaps, provided by one or more distinct sellers too, then they simply (1) INSERT a new row in the ProductSeller table including the corresponding SellerId and ProductNumber (taken from Product.ProductNumber) values toghether with the complementary column values that contain the product information as designated by the seller at hand.

So, as explained above, the ProductSeller.ProductNumber and ProductSeller.SellerId would be very useful to cover the following specification:

Admin users will be required to identify that products are the same across various sellers.

Since having a given ProductNumber value referenced from multiple ProductSeller rows will assist your database users in the identification and retrieval of those products that are offered by different sellers, who will be recognized by their distinctive SellerId value which, of course, points to a precise Party.PartyId (or Organization.OrganizationId).
4. Tracking Product data modifications
4.1 Product History and ProductSeller History
Since it seems reasonable to assume that product data can suffer modifications over time (and, probably, most of the time these modifications will be related to product prices), I deem necessary the inclusion of a mechanism to track these changes. This is the reason for which I incorporated the entity types denominated ProductHistory and ProductSellerHistory in the above presented logical model.
In this respect, you may find this post about temporal databases and this post about data versioning of relevance.
4.2 Handling ProductPrice as a time series
You might be face the need to follow the trail of time-related product modifications exclusively when they concern to prices, so you could alternatively make use of the model depicted in Figure 3, which you can download from Dropbox as a PDF, as well.

In this way, let us take the Product and ProductPrice entity types as references. As demonstrated in this alternative model, I have moved the DefaultPrice attribute to a new entity, and this new entity has a PK made up of ProductNumber (also playing the role of a FK that makes a reference to Product) and CreatedDateTime (that would mark the exact point in time in which a determined default price was inserted).
Consequently, every time that a default price value needs to be put up-to-date, your database users only have to INSERT a new ProductPrice row, containing the suitable ProductNumber taken from Product and the new price value. You may use a server function so that you can retrieve and store the exact insertion instant, i.e. CreatedDateTime, in a reliable manner.
The current or effective version of a product default or base price occurrence would be the row holding the MAX(ProductPrice.CreatedDateTime) for a definite ProductNumber,  and all the other rows (when they exist) matching the same ProductNumber would be the previous DefaultPrice versions.
You can then employ a comparable approach to handle the changes related to the ProductSeller price datum.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical papers authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship theory, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that this techinque was formalized by way of first-order logic.
2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3. The use of role names is recommended since 1970 by Dr. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.


Change database used for SQL Server Integration Services logging The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here)

How do I make this change? I wasn't able to find a setting for this. I'm using SQL Server 2012.

Blowing Kin's comment up to an answer...

Logging in the package deployment model (2005, 2008/2008R2 and potentially 2012/2014/2016 depending on how the project is defined) is done through an explicit request within each package.

A package looks something like this. At the bottom, you see 3 entries under Connection Managers: msdb, SO_34033143 and NotMsdb. The middle is a flat file connection manager, the other two are OLE DB connection managers. All a Connection Manager does is define the specifics of the entity the package is going to work with: this database on that server logging in as user123. A CSV named XYZ.csv, etc.

It's important to note that the name given to a Connection Manager may have no bearing on what it is actually connecting to. For example, the one named "msdb" is actually pointing to tempdb because I'm lazy.

If you right click in the grey background between the Data Flow Task and the Connection Managers, you should get a context sensitive menu and pick Logging

In the resulting screen, you should see a menu pop up that has on the right hand side "SSIS Log Provider for SQL Server" checked. The third column indicates the connection manager that is being used to log SSIS.

What you'll want to do and again, this is for every package, go in and change the connection manager from the thing that is pointing to msdb and either use an existing connection manager or add a dedicated connection manager into the packages.

Personally, in my pre-2012 environments, I advocated for an SSISConfig/SSIS_Config database. I used that for logging and configuration across a given tier/class of servers (dev/stage/prod).

If you're handy with scripting, you can programatically open every SSIS package and change the logging provider but it's not for the faint of heart. I don't believe I have that code online but if you're super desperate, ping me via comment and I'll see if I can find that hard drive.


How to design a persistent table with dependencies which are allowed to be deleted in the future? I am designing a database that archives all class schedules and student attendances based on these schedules.
So here are the tables.

Teacher, Subject, Classroom, Class

Schedule table has many-to-many relationship to the above tables.

Schedule:
  date (date),
  start_time (time),
  end_time (time),
  teachers (m2m to Teacher),
  subject (foreign key to Subject),
  classrooms (m2m to Classroom),
  student_classes (m2m to Class)

My issues is that the schedules must be persistent. However, the teachers may be deleted from the system. How do I design my dependencies so that removing them will not affect the schedules. Currently all relationships use integer primary key.

This looks like a case where you might create Foreign Keys with set null on delete.  From that post:

  What is a foreign key with "Set NULL on delete" in SQL Server?

  A foreign key with "set null on delete" means that if a record in the
  parent table is deleted, then the corresponding records in the child
  table will have the foreign key fields set to NULL. The records in the
  child table will not be deleted in SQL Server.

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

Just make sure that Teachers in the Schedules table is defined to allow nulls


How to restrict data pump logon to localhost I have a user in Oracle 12c database that will be used to run full data pump export and import. I would like to restrict logons from that user to localhost.

I have tried to create logon trigger:

create or replace trigger logon_trigger
  after logon on database
declare
  host varchar2(50);
begin
  host := trim(lower(sys_context('USERENV', 'HOST')));
  if lower(user) = '<data pump user>' then
    if host <> '<hostname>' then
      raise_application_error(-20000, 'Login not allowed');
    end if;
  end if;
end;
/

I can see from the audit trail that this trigger has fired (ORA-20000: Login not allowed) but the user is still allowed to logon from remote host.

This restriction works for normal users but not for the data pump user. I suspect this has something to do with the roles exp_full_database and imp_full_database.

Is there some way to make this trigger work or should I use local OS authentication?

ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (Doc ID 265012.1)

ADMINISTER DATABASE TRIGGER Privilege Behavior with Database Logon Trigger
--------------------------------------------------------------------------
Logon triggers can be used to mediate database access: when the restrictive
conditions are not met, an application error with a message is raised that
causes the logon to be denied.

...

However, we need to keep at least one user who can still connect when there is
a problem : a fallback mechanism must exist where an administrative user is
exempt from such errors of a prohibited connection.

Any user granted the ADMINISTER DATABASE TRIGGER system privilege can still
connect : instead of getting the error causing the session to be terminated,
the error is recorded in the alert.log and a trace file in user_dump_dest.

And:

select * from dba_sys_privs where privilege = 'ADMINISTER DATABASE TRIGGER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
WMSYS                          ADMINISTER DATABASE TRIGGER              NO
DBA                            ADMINISTER DATABASE TRIGGER              YES
SYS                            ADMINISTER DATABASE TRIGGER              NO
IMP_FULL_DATABASE              ADMINISTER DATABASE TRIGGER              NO

So yes, you can try local OS authentication.


Implementing a Record Keeper/Rollback System for a MySQL Database What's the best way to create a record keeping table for all actions in my database?

I have a number of tables inside one database and I want to create another that records all movements in every other table - adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button.

Is there a tried and tested method for this or a native feature of (InnoDB) MySQL useful for this?

If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.

The idea of being able to roll back individual changes to a database is problematic.  Records are related to other records in RDBMS, so rolling back one change may cause referential integrity problems.  This means that any kind of rollback mechanism is going to have to be smart enough to know what the referential integrity constraints are and either cascade rollbacks or prevent rollbacks that would cascade (depending on your business rules).

Another approach which you might want to consider instead is to put all changes into a work queue and let the administrator approve them before they are written into the database.  In this way the contents of your database would be "moderated".  If there is a very high risk of data changes being rejected then the pre-approval approach may be a good one for you.

On the other hand, if it would be rare for a change to be rejected, then really what you want is an auditing system that records changes.  Rolling back a change is difficult, so many it just needs to be done manually.  The important thing is knowing what changed and how - and perhaps who changed it so you can avoid undesirable changes in the future.

A common approach for auditing changes to data is to have a history/audit table for each table that you are tracking.  The structure of the history/audit table is the same as the main table, but it adds auditing columns, typically:

Date/Time of the change
User who made the change
Change action (insert/update/delete)
Source of the change (if multiple systems/sources can change the data)

Essentially every time there is a write into the main table, the new main table values are also recorded in the audit table, with the extra columns filled out.  In the case of a delete, use the last known values.  Sometimes the audit record is written automatically by a trigger, sometimes people write application logic to record this information.


Creating a test database from Production database (different machines & servers) I'd like to set up a test database on a separate server. Production database is MS SQL Server and the test server is a *nix box so I'd like to have the test server running either PostgreSQL or MySQL.

The dataset is pretty large; some of the tables have 30k+ rows and there are a lot of tables.

Some of the tables don't change much at all, and many of the tables can be empty for the purposes of testing, so ideally some kind of solution that can copy some but not all of the records from a database would be ideal.

Barring that, I may just make a test copy of the production database on the same server rather than putting the test db server on the development server. Are there potential problems with hosting the testing database on the same server as the production server, assuming that the load on the testing database will be relatively minor?

And if I am copying from production -> test within MS SQL Server, is there some kind of automation script I can set up that selectively copies only some of the records (for example, only the last 1k rows from some tables, none from others, and all rows from a selected few)? Is there a way to set this up as a selective backup, and then use that backup to generate the test database?

Additional info

The thing being tested here is just the code, not the database or even the interface into the database. The code (Django) uses an ORM so it doesn't matter which RDBMS is being used. I'm comfortable with assuming that if I call .save() it will work regardless of the database; my concern is whether I have the forms, utility functions, data imports, etc. set up correctly and all of these are abstracted through Django's relational model system which has been shown to be completely indifferent to RDBMS (indeed, in the production version of one of my sites I am pulling from both a MySQL and a SQL Server database at the same time (one contains legacy data).

Further to my comment this is, putting it as politely as possible, a moderately crazy approach to getting the job done.

Regardless of how mighty the ORM is, you are inherently changing the behaviour of the system by changing the RDMS.
That your ORM can talk to two different database systems is a different proposition to the same database (i.e. schema) on two platforms. You can certainly create a RDBMS agnostic application but you'd never release it having tested against only one, would you?
To accommodate the change of RDBMS, you create a stack of unnecessary ETL work to re-create the database objects, manage the type changes and then finally shift the data around.

  Are there potential problems with hosting the testing database on the
  same server as the production server, assuming that the load on the
  testing database will be relatively minor?

You could mitigate the inherent risk in mixing development and production with appropriate segregation of security rights but accidents happen, people make mistakes, the wrong button get's clicked. If the server is comfortably spec'd and you are 100% convinced your testing won't place an undue load on the server, you could consider installing a second SQL instance side-by-side with the production instance but make sure all interested parties accept the risks first.

Alternatives:

VirtualBox or similar. Better isolation if you have to run this on your production server and you keep the database in SQL Server.
If you can't buy, rent. Rackspace Cloud, Amazon EC2, Microsoft Azure.


ora 20001 error in oracle dbms scheduler i have written a test job like

begin
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,job_type =>'plsql_block'
,job_action => 'null'
,enabled => true
);
end;
/

after a while when the check the status

select * from user_scheduler_job_run_details where job_name='TEST_JOB';

the status is failed ,i see these two lines

ORA-20001: You are not allowed to logon from this terminal!!!
ORA-06512: at line 39

how can i resolve this error.
and
how to find the job id of a scheduled job(created using dbms_scheduler)

Your question contain 2 items that must have been looked at before question itself arose:

Have you looked at API of DBMS_SCHEDULER (any programmer should do that before knowing how to use built-in [package]) ; another words have you RTFM?
Oracle (most of the time) is in-fact very good with "responses" - error signaling back to "client". Have you googled ora - 20001 ?

To your question:

Your job action is NULL; you need to have some procedure that does something AND also is not named as Oracle keyword or PLSQl block that must have at bare minimum begin and end
- docs
Oracle error 20001 is custom error and if you want to see the code you need to contact DBA's.
Based on the message , seems that your schema also missing some grants (you obviously logged in to the database , so message itself is deceiving , but the exact error can only be identified by going thru the code or checking with DBA's directly - see above )
why do you need job id? your job name is providing your with information already! check API's!!


*.bak file on another server file share On a physical Dell server, I am trying to setup a backup job to *.bak file on to failover server.
In the video, DBA Fundamentals - backup 3 - setting up maintenance plans @6:24 backing up to a local drive but need to be on failover server.
I created a file on to another server drive \\mssql\backup. I try to run a backup file on to another physical server and it will not backup file over .bak file to failover. Do you have any recommendations on how to do this?
I need a T-SQL script in my management jobs to create .bak file over to failover server so I do not have to copy and paste .bak file. Used in agent.
I am trying to test the disaster recovery .bak for mcafee epoilcy orchestrator with MSSQL database using the backup file.

It's "Most Likely" a Permissions Issue
If you're performing a network SQL Server backup to a file share you'll most likely need to do one or more of these three common (A,B,C) tasks below:
A.> SQL Server Service
If you're running your backup from a SSMS query window, you need to make sure that the SQL Server service is using an Active Directory account that has permissions to write to the share location you're writing to.
B.> SQL Server Agent Service
If you're running the backup from the SQL Agent with a schedule job, you need to make sure that the SQL Agent is using an AD account that has permissions to write to the target share.
C.> Trusted Connection with sqlcmd
If you're running sqlcmd you need to use the -E and make sure the account that's running sqlcmd (the account running the cmd window) has permissions to write to the target share.
SQL Server Configuration Manager
If you need to change your SQL Server services to use an AD account, you'll need to apply the AD account using SQL Server Configuration Manager (SSCM). SSCM configures other things on the Windows Server besides just adding the AD account. After you change the associated service account, you'll need to restart that service for the account to take affect.
Workgroups instead of AD
If you're using a Windows Workgroup environment instead of AD, you'll want to make sure that both the Windows SQL Server and the target Windows box, have separate but identically named accounts on them with the same passwords. Run the SQL service you're using for the backup with this account.
Target Server Permissions
If you're unsure that the SQL Service/Agent accounts has the right permissions, just recreate the share on the target Windows machine and give that account at least read/write access to the share location using the folder's properties --> Share tab --> Share ... button.
Note
All that being said, if you're configuring backups for an AlwaysOn configuration there are  separate gotchas that apply depending on which SQL Server is primary and which one is secondary and how you have backups configured. But, based on the video you presented, I don't think you're having issues with AlwaysOn backups.


simpler way to write if today or tomorrow is between ColA or ColB SELECT * FROM my_table WHERE
getdate() between SPRFRDTE and SPRTODTE
or dateadd(DD, 1, getdate()) between SPRFRDTE and SPRTODTE

is there a way to write this as if TODAY or TOMORROW is between SPRFRDTE and SPRTODTE rather than if TODAY is between SPRFRDTE and SPRTODTE or if TOMORROW is between SPRFRDTE and SPRTODTE

The issue is how to check for overlapping ranges:

WHERE SPRFRDTE <= dateadd(day, 1, cast(getdate() as date))
  AND cast(getdate() as date) <= SPRTODTE
  AND SPRFRDTE <= SPRTODTE ;                 -- this might not be needed.

The very last condition is just a technical detail and may not be needed if you are sure (i.e. there is a constraint) that enforces that CHECK (SPRFRDTE <= SPRTODTE).

If however such abnormal values are allowed, you need to include that condition as well for the query to be equivalent to the version with the two BETWEEN.


Can alias in SQL Server contain slash? I'm not sure if there is such a question that I can't seem to find. I have a clustered instance named FC\SQLSRV. Because this is the virtual name so I can't create an alias for the clustered instance. I have another SQL Server database server (B) using the default name instance (MSSQLSERVER). What I want is to create the alias for the server B using the same clustered instance name. The reason is I just want to switch my application (that is connecting to the server B) to use the clustered instance.

You can create a client alias on the application server. You will have to run CLICONFG.EXE on the client, click add on the alias tab, click TCP/IP add the server alias B and set the connection paramters to be FC\SQLSRV after this all connections to server B from that client/application server will be forwarded to FC\SQLSRV If your application is 32bit you will also have to do the same for the 32bit registry and run %systemroot%\syswow64\cliconfg.exe


Database not going into single_user mode I have a corrupt database with wrong checksum error. When I try to put database into single_user mode to repair it gives following error.
I am using following sql command from management studio to put it on single_user mode.

Alter database testDb set single_user with rollback Immediate;

But it just returns with following

  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.
  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdd7144d; actual: 0x4ff829f5). It occurred during a read of page (1:314) in database ID 5 at offset 0x00000000274000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\DreamBackup.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

when I run dbcc checkdb it returns the following:

  Msg 7985, Level 16, State 2, Line 1
  System table pre-checks: Object ID 3. Could not read and latch page (1:296) with latch type SH. Check statement terminated due to unrepairable error.
  DBCC results for 'dreambackup'.
  Msg 5256, Level 16, State 1, Line 1
  Table error: alloc unit ID -3054820568275288064, page (1:296) contains an incorrect page ID in its page header. The PageId in the page header = (10289:856300870).
  CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
  CHECKDB found 0 allocation errors and 1 consistency errors in database 'DreamBackup'.

From Comments

As this error is related to I/O and most likely this has to do with server hardware where your database is hosted.

Please try putting database into emergency mode before setting into single user mode with below command:

ALTER DATABASE testDb SET EMERGENCY

Once database is set into emergency mode, try to put database into single user mode with below command:

ALTER DATABASE testDb  SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

try to run dbcc checkdb now and see if it works. In case, it doesn't work and throws error. Take the Full backup of database if single user mode is successful otherwise take it when emergency mode is turned on. Copy this backup to some other server with same or higher SQL server version and restore the same.

Once successfully restored, perform dbcc checkdb and see if it can be repaired. Repair depends on type of issue with your database. Since you don't have any backup, I guess you will have to go with minimal loss of data/log based on error type.

Please refer this link from Paul Randal for more details on repair of database.

Hope above helps.


RDS innodb_flush_log_at_trx_commit problem I use Amazon RDS as mysql db backend for highload project and try to optimize insert performance for innodb tables.
According to information from different sources - good candidate to speed up inserts is innodb_flush_log_at_trx_commit parameter.
My local tests show significant boost when I set it to 2 (or 0), but changing this param on RDS has no effect.
What may be a problem?
I tried all available values and results are the same (30-40 inserts per second into empty table) while local tests show ~9000 inserts per second.
I also tried M3.large General Purpose SSD RDS test instance (0 load) with same result.

Complain to RDS.

Meanwhile...

Plan A:  Design code to gather the rows up and do a multi-row INSERT or LOAD DATA.

Plan B:  Put BEGIN and COMMIT around small batches of rows.  (Suggest 1 second or 100 rows or 1MB, whichever comes first.)


How to create 3 columns from 2 columns with SQL query? I have the following table, Table1:

Id, Speed, Power
1, 1000, 100
1, 2000, 150
1, 3000, 200
2, 1000, 125
2, 2000, 175
2, 3000, 225

I would like to know SQL syntax to return the following:

Speed, Power600V, Power750V
1000, 100, 125
2000, 150, 175
3000, 200, 225

I tried this:

SELECT Speed, Power as Power600V From Table1 WHERE Id=1
UNION
SELECT Speed, Power as Power750V From Table1 WHERE Id=2

It isn't returning the data the right way though.

You need to use a pivot table to get colums out of rows for what you're after

select speed, idp.[1] as power600V,idp.[2] as power750V
from tableName PIVOT
(
MAX(power)
FOR id in([1],[2])
) as idp

will get you the results you're after

https://technet.microsoft.com/en-us/library/ms177410 for the full pivot reference


postgres: permission denied for schema I am new to postgresql and I would grateful if
you could please advise on how-to resolve the following error..

I have issued the followed commands:

ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA net FROM PUBLIC ;

ip_spotlight# GRANT USAGE ON SCHEMA doc TO netopsapp ;

ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA doc GRANT ALL ON TABLES TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA net GRANT ALL ON TABLES TO netopsapp ;

Below is a list of the privileges:

ip_spotlight# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 doc    | postgres |                      |
 net    | postgres |                      |
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(3 rows)

And the error is:

python3 -m pwiz --engine=postgresql --host=x.x.x.x --port=5432 --user=netopsapp --password  --schema=doc --tables=bgp_communities ip_spotlight
Password:
Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: permission denied for schema doc
LINE 1: SELECT * FROM "doc"."bgp_communities" LIMIT 1

Could you please advise on how to setup the privileges so that netopsapp user to have access to the tables defined in schema doc

PS: the first 2 commands were mentioned as best practice in the postgresql book

this solves it:

postgres=# \connect ip_spotlight
You are now connected to database "ip_spotlight" as user "postgres".
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ;

ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA doc,net TO netops ;
ip_spotlight-# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA doc,net TO netops ;

ip_spotlight-# GRANT ALL ON ALL TABLES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL SEQUENCES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL FUNCTIONS IN SCHEMA doc,net TO netopsapp ;


Determine if user-defined type is ENUM Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM?

Essentially we have the following:

CREATE TYPE foo AS ENUM (
  'Sometimes',
  'You',
  'Wanna',
  'Go',
  'Where Everybody Knows Your Name'
);

With a table instantiated by:

CREATE TABLE bar (
  lyrics foo DEFAULT 'Wanna'::foo
);

I'm able to determine the type of foo from the column lyrics, however, I'm having trouble finding a way to determine whether or not foo is an ENUM.

For context, I need this information to programmatically get a list of all possible values of foo when given a column of lyrics.

As often with such questions, the \set ECHO_HIDDEN on command of psql helps.  \dT+ will show the possible values of the enum, if the type in question is an enum.  The query behind the output is rather complex, but one can simplify it to fit your needs like

SELECT format_type(t.oid, NULL) AS name,
       array_agg(e.enumlabel ORDER BY e.enumsortorder) AS elements
  FROM pg_type AS t
  LEFT JOIN pg_enum AS e ON e.enumtypid = t.oid
 WHERE t.typname = 'foo'
 GROUP BY t.oid;

This will return {NULL} (an array with a NULL in it) if the type is not an enum, and the actual elements as an array otherwise.  You can tweak it further if necessary.


I'm going to wrap database trigger's source code, command properly run but source not encrypt I'm going to wrap my source of database logon trigger. i run the command,
"wrap iname='E:\block_tools_trigger_HR.sql' oname='E:\block_tools_trigger_HR.plb'".
plb file created without any error but code not encrypted.

Source code of trigger is given blow:

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;

Oracle's wrap program doesn't wrap triggers. The way to do this is to move the code into a package or a stand-alone procedure, and make the trigger a one line call to invoke this code.

See the documentation on the limitations of wrapping: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS01602

In your case, something like the following (untested) should work:

CREATE OR REPLACE PROCEDURE block_tools AS
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
CALL block_tools;

All I've done is move all the code into a stored procedure, and redefined the trigger to CALL that procedure.


mysql 8.0, updates are sometimes stalling Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, innodb_flush_log_at_trx_commit or even setting innodb_flush_method to a wonderful knob O_DIRECT you discovered recently, this is not the case.
Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack during these periods.
Mysql config is the same, hardware is similar (same CPU, same amount of RAM and its type, same disk model (only the size is increased)), so the only two thing that changed is the FreeBSD release (11.x -> 12.x) and MySQL version (5.7.x -> 8.0). I really doubt this has something to do with FreeBSD 12, so I assume it's the MySQL 8.0 specifics.
Looking at the server performance I can say there's plenty of CPU available (at least 30% or more) and disk i/o is saturated at 50% at it's most. So the question is - what's in the 8.0 that makes updates to stall at some moments ?
Here's the update statement and it's profile:
UPDATE mp_user SET ts_update = NOW(), latitude = '42.962993621826',
longitude = '47.550437927246', accuracy = '515.91363525391',
source = 'lbs', batteryLevel = '81', steps = '0', turnovers = '0',
setCoordsTime = NOW() WHERE id = '3185997';

'starting','0.000294'
'Executing hook on transaction ','0.000072'
'starting','0.000066'
'checking permissions','0.000068'
'Opening tables','0.000144'
'init','0.000097'
'System lock','0.000095'
'updating','7.698311'
'end','0.000270'
'query end','0.000115'
'waiting for handler commit','0.012015'
'closing tables','0.000301'
'freeing items','0.000185'
'cleaning up','0.000108'

Here's the table structure:
Create Table: CREATE TABLE `mp_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `ts_update` datetime NOT NULL,
  `lastWakeUpPush` datetime DEFAULT NULL,
  `type` enum('parent','child','admin') NOT NULL,
  `latitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `longitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `accuracy` float(10,2) NOT NULL DEFAULT '0.00',
  `source` enum('gps','wifi','lbs') DEFAULT NULL,
  `batteryLevel` int(4) DEFAULT NULL,
  `steps` int(4) DEFAULT NULL,
  `turnovers` int(4) DEFAULT NULL,
  `setCoordsTime` datetime DEFAULT NULL,
  `phoneLogin` varchar(45) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `salt` varchar(15) DEFAULT '',
  `licenseTo` datetime NOT NULL,
  `monthForRepostUsed` tinyint(1) NOT NULL DEFAULT '0',
  `monthForQuizUsed` tinyint(1) NOT NULL DEFAULT '0',
  `isTrial` tinyint(1) NOT NULL DEFAULT '1',
  `deviceUid` varchar(10) DEFAULT NULL,
  `warnings`   set('backgroundDataOff','geoDeviceOff','geoAppOff','noSim','noMobileDataTransfer','pushesOff','batteryOptimization','mic','appStats','noGoogleServices','backgroundServicesOff','wifiOff','accessibilityOff') DEFAULT NULL,
  `locale` enum('en','en-US','ru','ar','zh','cs','nl','et','fi','fr','de','el','he','hu','id','it','ja','kk','ko','lv','lt','pl','pt','ro','esp','sv','tr','uk','vi','bg','sr','pt-BR') NOT NULL DEFAULT 'ru',
  `lastNcPush` datetime DEFAULT NULL,
  `priceGroup` int(11) DEFAULT NULL COMMENT 'Foreign key to mp_geoZonePriceGroup',
  `healthCheckTime` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneLogin` (`phoneLogin`),
  KEY `ts_update` (`ts_update`),
  KEY `setCoordsTime` (`setCoordsTime`,`type`,`lastWakeUpPush`),
  KEY `deviceUid` (`deviceUid`),
  KEY `batteryLevel` (`batteryLevel`),
  KEY `mp_user_mp_geoZonePriceGroup_id_fk` (`priceGroup`),
  KEY `lastLoginidx` (`lastLogin`)
) ENGINE=InnoDB AUTO_INCREMENT=9465064 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this way it can use up to all the amount of available memory.

Did the trick for me. Unfortunately, I wasn't the guy to figure it out, but rather guys from paid percona support desk. I was told this is likey to happen on huge tables.


phpMyAdmin Error After Upgrade to 3.5.8.1 I'm a newbie in MySQL. I use it only for PHP.

I tried to upgrade myphpadmin 3.5.8.1. After doing that, I couldn't access my database and got the following error:

  #1064 - 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 'WHERE db_name = 'happy2'  AND table_name = 'foto'  ORDER BY version DESC' at line 1

My Other ALL DB have error like that.

Can anyone help me?

I had this problem as well, when downgrading phpMyAdmin. When I deleted my cookies, the problem went away.


Auditing authentication issues in a distributed environment I'm trying to get a better handle on what locked out a specific user when multiple Oracle databases are involved using database links.

Setup:

sqlplus kjohnston@db2

create user testuser identified by "Dummypass1";
grant create session to testuser;
exit;

sqlplus kjohnston@db1
create public database link testlink using 'DB2';
create user testuser identified by "Differentpass1";
grant create session to testuser;
exit;

sqlplus testuser@db1

Run the following a few times until testuser@db2 is locked.

SELECT * FROM dual@testlink;

On DB2, I can query on the following:

SELECT users.username, users.lock_date, aud.os_username, aud.userhost, aud.sessionid, aud.os_process
  FROM dba_users users
  JOIN dba_audit_trail aud
    ON aud.username = users.username
   AND aud.timestamp = users.lock_date
 WHERE users.account_status in ('LOCKED', 'LOCKED(TIMED)')
   AND aud.action = 100
   AND aud.returncode = 1017;

USERNAME    LOCK_DATE           OS_USERNAME USERHOST    SESSIONID   OS_PROCESS
TESTUSER    09-MAR-17 09.39.14  SYSTEM      VM1         10484322    404:5808

In this case, it's obvious that this lock originated from a database link since the OS_USERNAME is SYSTEM and the USERHOST is VM1, which is a database server (so few people have access to it anyways) where database DB1 is running from.

However, i'm unsure where to go from here in my analysis.  Sure, I have the SESSIONID and the OS_PROCESS from dba_audit_trail (as shown above), but I've been unable to successfully query the appropriate table on DB1 in order to narrow down what was running and who was running what on DB1.

I've tried querying v$session (on SID and AUDSID columns) and v$active_session_history (on SESSION_ID) on both DB1 and DB2 in an attempt to get more information.  However, all attempts to query these tables on either database using either SESSIONID or OS_PROCESS values have failed (0 results returned).

Since there are no private database links on DB1 that connects directly to testuser, it looks like this probably originated from a public proxy database link, but i'm unsure how to find what testuser@db1 was actually running at the time that testuser@db2 got locked out.

Also, consider the possibility that testuser doesn't equate to a specific person, but it is an application schema that many different programmers may have access to.

Also, the logged in user that ran the dblink connection may not even be TESTUSER@DB1 since a user with create any procedure privilege could have:

create procedure testuser.testproc
  v_var varchar2(1);
begin
SELECT dummy
  INTO v_var
  FROM dual@db2;
end testproc;

exec testuser.testproc;

Any ideas how I can track down what locked out testuser@db2 (the SQL that was running, preferably) given my supplied example?

As per the definition of your database link, it's a fixed user database link.

If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.

For example, you have connected as kjohnston and created a fixed user private database link, If kjohnston uses the fixed user link in a query, then kjohnston is the user on the local database, but you connect to the remote database as testuser.

So you can't get the session information of testuser in the local database. DB one is just acting as client tool for the testuser.

SQL> SELECT users.username, users.lock_date, aud.os_username, aud.userhost, aud.sessionid, aud.os_process
  FROM dba_users users
  JOIN dba_audit_trail aud
    ON aud.username = users.username
   AND aud.timestamp = users.lock_date
 WHERE users.account_status in ('LOCKED', 'LOCKED(TIMED)')
   AND aud.action = 100
   AND aud.returncode = 1017;  2    3    4    5    6    7    8

USERNAME               LOCK_DATE
------------------------------ ---------
OS_USERNAME
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
 SESSIONID OS_PROCESS
---------- ----------------
TESTUSER               10-MAR-17
oracle
testserver1
   1590012 3348

You can get the locked username using the above query.

On DB1, you can query the dba_db_links view to know who is the owner of that dblink.

SQL> select * from dba_db_links where username='TESTUSER';

And you can get all the SQL fired by that user using either auditing or tracing.

  I've tried querying v$session (on SID and AUDSID columns) and v$active_session_history (on SESSION_ID) on both DB1 and DB2 in an attempt to get more information. However, all attempts to query these tables on either database using either SESSIONID or OS_PROCESS values have failed (0 results returned).

You can't get information from these views on DB1 because that information is belongs to testuser which has created session on DB2.


Which server is used in script? In MS SQL Server, if I'm connected to two servers (for example dev and production) that both have a DB called MyDb which server is targeted when I run a  script like

use MyDb

SELECT Description
FROM MyDescriptions
WHERE Description LIKE '%something%'

I'm using SSMS. I'm not asking about how to get the information but rather what the consequence will be.

Each tab in SSMS is either linked to a SQL Server instance or working offline (disconnected). Whenever you open a .sql file in SSMS, it will automatically try to connect to the selected Instance and Database on your Object Explorer. When executing a query while offline it will pop up the connection window.

You can see the current connected instance if you hover the mouse over a connected tab or in the status bar at the bottom (default), next to the username and SPID, if you didn't hide it.

Scripts can't store the initial instance's connection. So make sure to connect previously to the proper instance. You might use sqlcmd inside scripts to connect to different instances but I don't think that's your concern.

There are add-ins like SSMS Tools in which you can map instances to color bars that are displayed on the side, up or down so for example you can map your Dev instance to color red and your Prod environment to green.