Cannot connect to an Hana database via its ODBC driver

you might require to install SAP HANA Database Client for ODBC configuration

Please refer to above linked document for a sample case where HDBODBC is installed for connection to HANA database via ODBC

After HDBODBC configuration is created as illustrated in referenced tutorial, you can build your connection string as follows referencing the System DSN name with DSN argument

"DSN=KodyazHANADb;SERVERNODE=myhdb.kodyaz.sap.biz:30815;UID=A00011462;PWD=myPassword+;DATABASENAME=K0D"

You can find the database name in SAP HANA Studio connection properties or by querying M_Databases system view


SQL Server 2016 CTP -> RTM, safe to attach CTP DB files (including SSISDB)?

You can safely attach any database from a prior build. As soon as you do this, the internal database structure will be automatically upgraded if there are any changes. This includes system databases, but they will not be considered system databases, using this method.

You can still retrieve SQL Server Agent jobs from the old msdb database if you need them, as well as anything you wanted from master, including things like logins.


Help with best practice of merging databases

Your best bet is to use Redgate's data compare to compare and insert data in the database your application is pointing to.

I have used this method in similiar situation and trust me ... this tool is a life saver.

You can dowload a 30 day full trial version and then purchase it if it suits your needs.


autogrowth set higher than available disk

SQL server would handle this aborting your transaction and throwing the following error:

I got this message from a test where I designed a scenario close enough to what you described:

I placed the database MyDB in a small disk with 100MB in size. Inserts in the table dbo.TextTable worked fine until the file needed to grow (500MB) in order to acomodate more data, but failed due to the lack of space in the disk.

Microsoft says that

  The autogrow setting cannot grow the database size beyond the limits
  of the available disk space on the drives for which files are defined.
  Therefore, if you rely on the autogrow functionality to size your
  databases, you must still independently check your available hard disk
  space. The autogrow setting is also limited by the MAXSIZE parameter
  you select for each file. To reduce the possibility of running out of
  space, you can monitor the Performance Monitor counter SQL Server:
  Databases Object :Data File(s) Size (KB) and set up an alert for when
  the database reaches a certain size.


SQL Server 2008 R2: query RECENT locks, NOT current locks

some (most) X locks are recorded in the log, see How to read and interpret the SQL Server log. You can see what xact acquired them and how long the xact lasted (see the link). You won't find the conflicts, but you can make some guess.


Oracle 11g XE - Using a stored procedure to grant privileges to a table

GRANT is a DDL statement. You can not run DDL statements directly in PL/SQL, but you can use execute immediate:

CREATE OR REPLACE
PROCEDURE "setUserPermissions" (u  VARCHAR2, l  INTEGER, tbl VARCHAR2)
AS
BEGIN
    DELETE FROM "userLvl" WHERE "user" = u;
    INSERT INTO "userLvl" VALUES(u,l);
    IF l>0 AND l<6 THEN
        execute immediate 'GRANT SELECT ON ' || tbl || ' TO ' || u;
        ...
    END IF;
END;


Real Time SQL Server Analysis Services Cube Processing Report

I guess you are worried about the downtime due to cube processing.
As detailed in the post below, the downtime, due to processing, is very minimal .

http://www.jamesserra.com/archive/2011/06/can-you-query-a-ssas-cube-while-it-is-processing/

In the comments section, there is an approach mentioned by which you can reduce this downtime (with few caveats) by using one server to do the processing and then sync the DBs.

If you still want to do the report, you can follow the approach suggested here:

https://stackoverflow.com/questions/11899250/how-to-determine-which-ssas-cube-is-processing-now


You have several problems with the query you listed:

In the first test, you use and AND instead of an OR or an IN (I perfer IN -- in fact, I would prefer it in all cases so that it is easy to extend later, but that is your choice)
You are nesting your case statements. You only want a single case statement, with multiple conditions. What you have is basically a nested if statement.
You never terminated the last case statement with an ELSE clause (I use ELSE 0 below, but in some cases, ELSE 1=0 OR ELSE FALSE might be needed to render a boolean value -- ELSE NULL should work too).
You have a LEFT JOIN that is converted into a JOIN because of the WHERE clause... I think that is what you actually want, but you should double check

The query should look more like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
JOIN progress ON progress.case_id = cases.id_cases
WHERE archive = 0
  AND done = 0
  AND CASE name
    WHEN 'Cranioplasty' THEN task_id IN (103006, 103002)
    WHEN 'Bone Tumor' THEN task_id = 201006
    WHEN 'Osteotomy' THEN task_id = 301002
    WHEN 'MBIO' THEN task_id = 401006
    ELSE 0
  END
ORDER BY name, date_surgery;

I think this is what you want. If you really want the LEFT JOIN, it would look like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
LEFT JOIN progress ON progress.case_id = cases.id_cases
    AND CASE name
      WHEN 'Cranioplasty' THEN task_id IN (103006, 103002)
      WHEN 'Bone Tumor' THEN task_id = 201006
      WHEN 'Osteotomy' THEN task_id = 301002
      WHEN 'MBIO' THEN task_id = 401006
      ELSE 0
    END
WHERE archive = 0
  AND done = 0
ORDER BY name, date_surgery;


It's not sufficient that 195.249.206.131 resolves to kommune.horsholm.dk that matches the wildcard.

After checking that, Postgres will try to resolve kommune.horsholm.dk and find that it doesn't resolve to 195.249.206.131 (as it doesn't resolve
at all in this particular case)

$ host kommune.horsholm.dk
Host kommune.horsholm.dk not found: 3(NXDOMAIN)

Doc quote:

  If a host name is specified (anything that is not an IP address range
  or a special key word is treated as a host name), that name is
  compared with the result of a reverse name resolution of the client's
  IP address (e.g., reverse DNS lookup, if DNS is used). Host name
  comparisons are case insensitive. If there is a match, then a forward
  name resolution (e.g., forward DNS lookup) is performed on the host
  name to check whether any of the addresses it resolves to are equal to
  the client's IP address. If both directions match, then the entry is
  considered to match


Consistency level refers to the number of replicas required, but the number of total replicas that exists is independent of the number of nodes. It is set by your Replication_Strategy. If your strategy for this keyspace only has 2 replicas then there are many situations where a CL of 2 would fail.

For nodes A, B and C

Imagine the data you want has been replicated to A and B.

But only A and C are alive. You can only get one replica even though 2 nodes are alive.


The problem might be a result of how you are defining your users.  I don't think the wildcards are working as you're expecting them to. The best way to figure this out, in my opinion, is to create a new user and password for the 18.61.10.64 host on the yoda.er.com MySQL Server.

Keep in mind that when logging in locally, the MySQL client on your server might be using the unix socket to authenticate.

mysql> show variables like '%socket%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

Connecting to MySQL

If this is the case then a password isn't necessary when logging in locally onto the MySQL server.

Could you try this from the machine you are trying to connect to. Use a new user and password combination because we are not sure how many different entries you might have for your power user.

GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'18.61.10.64' IDENTIFIED BY PASSWORD 'somepassword';

FLUSH PRIVILEGES;

Then from the shell on 18.61.10.64

mysql -u someuser -h ip.address.for.r2d2.er.com -psomepassword


MYSQL Multiple table optimization for advanced search

Don't use MyISAM, use InnoDB.  http://mysql.rjweb.org/doc.php/myisam2innodb
EAV is inefficient, especially when you normalize either of the columns.  http://mysql.rjweb.org/doc.php/eav  And read more about Entity-Attribute-Value schemas in stackoverflow.com
You are missing some critical indexes that are critical to performance.  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
At this point, FOREIGN KEYs are the least of the issues.


What are the main principles of SQL Server AGs depending on WSFC?

Should not the current WSFC host also be the primary AG server?

No.  The hosting of the core cluster resources is independent of the AG primary.  Generally speaking, it's not super important which server is the WSFC host when dealing with a WSFC cluster for SQL Server Availability Groups.

  How does the listener work? In our case, we have multi-subnet networking, and the listener's DNS name always resolves to 3 IP addresses among which one belongs to the current primary AG server, and works for connections. Does WSFC determine the listener's addresses?

This part mostly happens on the client that's connecting to SQL Server.  The client gets that same list of IPs from DNS, and then tries each of them until one succeeds.  The current primary server will respond and either accept the connection or redirect the client to another server in the cluster (depending on connection settings and other config like read-only routing).

  Is there any other principle (except quorum) that AG depends on WSFC?

There's a neat breakdown on this in the docs as well: Relationship of SQL Server AlwaysOn Components to WSFC

In general though, the concept of quorum (which I'll take to be a part of the larger health monitoring / failover concept) is the most relevant one for WSFC and AGs.


MongoDB is not using the index I created for this specific query

Try using $hint in your query to enforce your optimizer to use this specific index for the query and post the output.

Thank you!

-Anban


How to use transactions with SQL Server DDL?

Yes. Transactions apply to DDL and span batches.

I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error.

SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin Transaction
GO
exec sp_rename LogTable, LogTableOld;
GO
CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go
EXEC sp_recompile LogTable;
go

Commit;


Adding checkboxes in SSRS reports to filter values on a table

No. We do not have Check Box with Event Trigger and etc but you can use Hacks.

First Option is the most straight forward way of doing things. but for you to implement something like Check Box Routine you would need to hide and show check boxes and tables that have data.

You can Use parameter in you report, where you would bind your
category to the parameter and when user choose it then you would use
the parameter on the Filter option of the table.

Use with Avalable Values (default: Optional)

Then
Use The parameter on Table Filter

So user can Filter the Table Category.

You can use Hidden and Initial Toggle State properties with some Expression to hide and Show base on some sort of Toggle. (Not preferred)

I used Two Table, inside Another Table. set ToggleItem of Each ,to the Cell that would do the Toggle.

Result:

Design:

Row option:


Executing Powershell SMO against a cluster with elevated privileges to update service accounts

I ran into the same problem, looks like its an SMO bug (1st link) (however I have not tried the CredSSP stuff from the second link below)

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/265ee700-182d-4a99-96f8-cada117fd3d3

http://www.ravichaganti.com/blog/?p=1230

Please let me know if that works..


Is it possible to allocate data free for certain table?

You have not said whether you are using MyISAM or InnoDB.  I will assume InnoDB.

First of all, "Data_free" is a rotten metric.  It shows only one aspect of the free space.  There are about 3 other major contributors to "free" space, for which there are no metrics.

Second, if you are using innodb_file_per_table = OFF, then the only real metric is the size of the ibdata1 table.  "Data_free" has a radically different meaning for file-per-table versus not.  It sounds like you might have set that setting one way for some tables, the other way for others.

Third, tiny table allocate more space 16KB at a time; large tables grow by 4MB (or maybe 8MB?) at a time.  So the space will 'lurch', helping to make things unpredictable.

Give us a sample of the 'evidence'; we can probably explain what it means and sympathize with over how cryptic/deceptive it is.


Amazon Aurora MySQL conditional comment queries

Aurora runs as mysql 5.6.10a so the comment queries still work as expected in regular mysql


Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

max_connections is a global variable which will retain this value until you change it in my.ini and restart MySQL service.

Whereas concurrent connections can be measured by max_used_connections status variable which will vary based number of concurrent connections on your MySQL server.
This value is used to calculate Highest number of concurrent connections.
i.e. If this value is say 1000 and after sometime if number of concurent connections drops still this status variable will show 1000 until you execute Flush status query on your server.

To know exactly how many connections are open currently, you should check Threads_connected status variable.


Stored Proc Logical Reads Spiking Daily At The Same Time

The root was tracked to some old SSIS packages running longer than anticipated. Thanks for the feedback folks!


SQL Server could not find a login matching the name provided

I had the wrong port number. There was a third database instance with this port....

so it seems when you connect using 'SERVER\INSTANCE,port' it completely ignores INSTANCE if you specify port and instead of saying you have the wrong instance you get user not found.

thanks.


Why is my RDS mysql slow query log full of server restarts?

This is probably not cause for concern.

MySQL writes a new header to the file each time the logs are flushed.  Presumably this is just in case you rotated the log file, so the new file will have a header... but it doesn't actually check whether it's a new file or not.  The server does not have to restart to write this entry, so it doesn't mean the server is necessarily restarting.

The rdsadmin user you see in the processlist appears to be the supervisory connection that Amazon uses to monitor and manage each instance.  Something -- presumably that connection -- periodically rotates the log files, most likely with some variant of FLUSH LOGS;.  It sounds like the flush occurs more often than the rotate, which would exactly explain what you're seeing.

mysql> SHOW STATUS LIKE 'uptime';

This will give you the actual uptime of the instance in seconds.  If that value is high, this is just the server writing a new header when the logs are being flushed to disk.


How exactly does trigram word-similarity work?

It doesn't work like that, from the a mailing list thread in 2017-2018

  We searching for continuous extent of second string trigrams (in original orders) which has best similarity with first string trigrams.
  Possible solution could be forcing this extent boundaries to be at word
  boundaries. However, it would become less convenient to search for part
  of word. And we already have users adopt this feature.

The solution was to release pg_trgm 1.4 with new features which should drop with PostgreSQL 11. It will provide strict_word_similarity which will force extent boundaries to be at word boundaries. In addition there will be two new operators <<<-> and <<% which will do strict-word-extent operations on the index.

For more information see the docs on the development version of pg_trgm


Persistent Severity 016 alerts: "Change Tracking autocleanup is blocked on side table"

It seems to be a known issue and has already been recognized by Microsoft. You may read resolution as below:

Resolution

  A fix for this issue is included in the following updates
  for SQL Server:

  Cumulative Update 10 for SQL Server 2017

  Cumulative Update 3 for SQL Server 2016 Service Pack 2

  Cumulative Update 1 for SQL Server 2014 Service Pack 3

  With this fix, you will not see continuous messages any longer. The
  frequency of the error 22123 messages, if any, will not be more than
  once every 30 minutes. To completely eliminate these messages, enable
  trace flag 8293.

Listed are the version which has this issues:

  SQL Server 2017 Developer
  SQL Server 2017 Enterprise
  SQL Server 2017 Enterprise Core
  SQL Server 2017 Standard Windows
  SQL Server 2016 Developer
  SQL Server 2016 Enterprise
  SQL Server 2016 Enterprise Core
  SQL Server 2016 Standard
  SQL Server 2014 Developer
  SQL Server 2014 Enterprise
  SQL Server 2014 Enterprise Core
  SQL Server 2014 Standard

Please read more from link.

Hope above helps.


Try the following:

db.etudiants.aggregate([
 {
   $unwind: '$cours_reussis'
 },
 {
   $match: {
       'cours_reussis.note': {
           $gt: 60
       },
       'cours_reussis.nom_ecole': "ENSA"
   }
 },
 {
   $group: {
       '_id': {
           'nom': '$nom',
           'prenom': '$prenom',
       },
       'num_courses' : { "$sum" : 1 }
   }
 },
 { $match : {
      'num_courses' : { '$gte' : 3 }
  }
 }
])

Does this meet your criteria?

Note that the only changes I made to your aggregation were the following:

I limited the '_id' to just the 'nom' and 'prenom' fields
I used $sum to count the number of completed courses
The initial $projection stage was removed, as the aggregation can perform this optimization automatically.


Return int value from boolean field in SQL select

declare @b bit = 1

select convert (int, @b)

or

select cast(@b as int)

more


Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

Using OFFSET...FETCH will likely set a "row goal" in the execution plan.  Row goals are often a good thing, as SQL Server can choose a different plan based on the knowledge that it only needs a few rows.  See Paul White's series on row goals for lots of additional detail on that subject, which starts here: Setting and Identifying Row Goals in Execution Plans
Unfortunately, when there isn't good indexing in place, or the data being requested just doesn't match many of the rows in the tables involved, the introduction of a row goal can wreak havoc on performance.  This is because all, or most, of the table might need to be read just to get those 25 rows.
There's a great example of this on Erik Darling's blog: When Data Isn’t There
Your situation sounds like it matches this one closely.  If there are only 154 matches in the entire table (or set of tables), then without the row goal the optimizer might choose a parallel scan of the tables, using hash joins to bring the result set together.  With the row goal, the optimizer is likelier to choose a serial plan, with nested loops joins, which generally won't perform as well if a lot of data needs to be read.


Subtracting sets within a grouped column, should I be pivoting?

Simple self-join

What you want is something like this...

SELECT
  ARRAY[f1.type,f2.type] AS type,
  date,
  tp,
  greatest(f1.price-f2.price, '0.00') AS price
FROM foo AS f1
INNER JOIN foo AS f2
  USING (date, tp)
WHERE f1.type <> f2.type
ORDER BY f1.type, f2.type, tp;

I deviated a bit from what you desired. Generally, you don't want string concatenation like that. It's less useful. You're better off using an array. If you do want string concatenation just use f1.type || '_' || f2.type.

 type  |    date    | tp | price
-------+------------+----+-------
 {A,B} | 2010-10-01 |  1 |  0.00
 {A,B} | 2010-10-01 |  2 |  0.00
 {A,C} | 2010-10-01 |  1 |  0.00
 {A,C} | 2010-10-01 |  2 |  0.02
 {A,D} | 2010-10-01 |  1 |  0.00
 {A,D} | 2010-10-01 |  2 |  0.00
 ...
 (24 rows)

Crosstab

Cross-tab does something totally different. It only ever changes the display.

SELECT *
FROM crosstab($$
  SELECT
    ARRAY[f1.type,f2.type] AS type,
    date,
    tp,
    greatest(f1.price-f2.price, '0.00') AS price
  FROM foo AS f1
  INNER JOIN foo AS f2
    USING (date, tp)
  WHERE f1.type <> f2.type
  ORDER BY f1.type, f2.type, tp;
$$, $$VALUES (1),(2)$$
) AS t(type text[],"date" date,tp1 numeric,tp2 numeric);

Produces...

 type  |    date    | tp1  | tp2
-------+------------+------+------
 {A,B} | 2010-10-01 | 0.00 | 0.00
 {A,C} | 2010-10-01 | 0.00 | 0.02
 {A,D} | 2010-10-01 | 0.00 | 0.00
 {B,A} | 2010-10-01 | 0.48 | 0.00
 {B,C} | 2010-10-01 | 0.48 | 0.02
 {B,D} | 2010-10-01 | 0.48 | 0.00
 {C,A} | 2010-10-01 | 0.00 | 0.00
 {C,B} | 2010-10-01 | 0.00 | 0.00
 {C,D} | 2010-10-01 | 0.00 | 0.00
 {D,A} | 2010-10-01 | 0.00 | 0.04
 {D,B} | 2010-10-01 | 0.00 | 0.04
 {D,C} | 2010-10-01 | 0.00 | 0.06
(12 rows)

12 rows rather than 24. We pivoted and put tp1, and tp2 together. Pick your poison -- what kind of display do you want? I always prefer not using cross tab unless someone requires it.

Performance questions

  My current solution is a view that uses CTE(s) to create all the possible dates and type combos, and then I have a function that goes through every typeA and typeB, date, tp combo. It is very slow.

We would need to actually see the views, and to an output of EXPLAIN ANALYZE to know why it runs slow. Try the query I suggested at the top and see if it speeds things up.


How Datapump is faster than export/import backup?

Datapump is much more efficient than exp:

Datapump runs within the Oracle server processes, and can read directly from the database files and write directly to a file on the server. As I understand it, data access is direct, not via SQL.

exp uses the ordinary Oracle Call Interface (OCI) -- the library that Oracle provides to connect to the database. It can only run ordinary SQL statements like any other program.


How do query notifications detect changes in the query result?

Read the Mysterious Notification for an explanation on how it works. If you see the query executed every 10ms it means your application is running it every 10ms. My 8 ball is telling me your code does not check the notification EventArgs and is blindly resubmitting the query despite the Info being of the value Invalid, indicating a query than cannot be notified. Which is to be expected, since your query does not conform to the Query Notification restrictions:

  The projected columns in the SELECT statement must be explicitly
  stated, and table names must be qualified with two-part names.


Why is an additional filtered statistic being ignored (EAV schema)?

Filtered indexes and statistics won't come into play when you're using local variables, unless you use the OPTION (RECOMPILE) query hint, and are running SQL Server 2008 R2 or later.

Tim Chapman's MSDN blog post explains with examples.


For SQL Server 2012 where is better way to restrict rows (WHERE or JOIN)?

The best way is the one that matches your team's coding style. If you work alone then the best way is the one that you find to be the most readable. There is currently no performance difference for the queries in the question.

You can use the undocumented trace flag 8606 to see this for yourself. Here's the code that I'm testing with:

CREATE TABLE #A (X INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (X));

CREATE TABLE #B (Y INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (Y));

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y)
WHERE (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y) AND (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Not only do the queries have the exact same plan:

They also have the same internal input tree:

In other words, SQL Server rewrites the queries to be the same before optimization occurs. Therefore, there will not be a performance difference.


Updating Master table information based on Detail table status

Have you tried Update with join?

For example..

UPDATE TABLE1
JOIN TABLE2
ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID
WHERE TABLE2.BRANCH_ID IS NULL


pg_basebackup fails with " too many connections for role "replication""

Per comments, pg_roles.rolconnlimit is set to 1 for that role so it needs to be increased a bit to allow for several simultaneous connections.

Choose a more reasonable value, say 5 (or -1 for unlimited) and issue as superuser:

ALTER ROLE replication CONNECTION LIMIT 5;

...or connect with a different database user for pg_basebackup.


Multiset operators on nested tables with non scalar types

MULTISET EXCEPT

  The element types of the nested tables must be comparable.

Comparison Conditions

  Two objects of nonscalar type are comparable if they are of the same
  named type and there is a one-to-one correspondence between their
  elements. In addition, nested tables of user-defined object types,
  even if their elements are comparable, must have MAP methods defined
  on them to be used in equality or IN conditions.

The problem is, the above can not be done with a record type. record types have only fields, and can not have member functions. Furthermore:

Record Comparisons

  Records cannot be tested natively for nullity, equality, or
  inequality.

  These BOOLEAN expressions are illegal:

  My_Record IS NULL
  My_Record_1 = My_Record_2
  My_Record_1 > My_Record_2

This fails:

create or replace package p1 as
  type typ_Rec is record(id customers.customer_id%type, name customers.cust_first_name%type);
  type typ_NT is table of typ_Rec;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'

UNION works, because you did not specify ALL or DISTINCT, and in this case, UNION ALL is the default. UNION ALL does not need to perform comparison. But if you try UNION DISTINCT (which removes duplicates), that will also fail:

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset union distinct tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_UNION_DISTINCT'

You need an object type for this.

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20)
);
/

Type created.

create or replace package p1 as
  type typ_NT is table of typ_Obj;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'

Notice how it still failed, because I did not define the MAP function, I did not tell the database how it can compare two user-defined objects. Now try again, this time with defining MAP (and here I assume objects have unique identifiers and two objects equal if their identifiers equal):

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER
);
/

Type created.

CREATE or replace TYPE BODY typ_Obj AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;
END;
/

Type body created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Package body created.

And finally it worked, no warning.


Why do the databases go into the "recovery pending" state when the share permissions are changed from "everyone"?

It sounds like a simple permissions issue.

Why did this happen?

The Everyone permission literally allows anyone to connect to those network shares and access those files. When you remove those permissions, now no one is allowed to access them. What may be confusing is that you may think that SQL Server has access to those files and shares, when, in fact, it does not.

To allow specific access to those databases that run over SMB shares (starting with Microsoft SQL Server 2012), Microsoft recommends that the SQL Server service run as a <domain name>\<user name> and that this account have FULL CONTROL to the file share AND NTFS permissions to the underlying files and/or directories within that file share.

Alternately, Microsoft recommends that if you use a system account instead for the SQL Server service account then apply FULL CONTROL share AND NTFS permissions to the account <domain_name>\<computer_name>$--where computer name is the computer name of your SQL Server.

I'd retry removing those "everyone" permissions once more, in a test environment, to validate that this fixes your issue.

For more details see:

https://msdn.microsoft.com/en-us/library/hh759341.aspx


How do I show warnings when loading data created from mysqldump?

You should startup mysqld with log-warnings (it should be on by default) and also the max error count

[mysqld]
log-warnings
max-error-count=9999999999

You may have to start the mysql client with

SET SQL_WARNINGS = 1;

This is not mysqld setting, it's a mysql client session setting.

You may want to add show-warnings to mysql client session

mysql -u... -p -hlocalhost --show-warnings


SQLite writing a query where you select only rows nearest to the hour

Assuming that you want a SQL-only solution and your table is defined as:

CREATE TABLE measurements (
  pk INTEGER PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  value TEXT NOT NULL,
  sensor_id INTEGER);

then create the following views:

CREATE VIEW vw_measurements AS
SELECT pk, timestamp, value, sensor_id,
       3600*ROUND((timestamp+1800)/3600) AS hour,
       ABS(timestamp - 3600*ROUND((timestamp+1800)/3600)) AS distance
FROM measurements;
CREATE VIEW min_distances AS
SELECT hour, MIN(distance) AS min_distance
FROM vw_measurements
GROUP BY hour;

Then you can get the records closest to every hour as:

SELECT m.hour, GROUP_CONCAT(m.timestamp), GROUP_CONCAT(m.value)
FROM vw_measurements m, min_distances d
WHERE m.hour = d.hour AND m.distance = d.min_distance
GROUP BY m.hour;

WARNING: SQLite SQL lacks many useful constructs that other SQL variants have, so the solution above can't discriminate between records with timestamps equidistant to their closest hour (either same timestamps or hour±samedelta); ergo the GROUP_CONCAT aggregate. Typically each row will have a single timestamp and value.

Note that if you have many thousands of records, the performance can be abysmal (tested on SQLite 3.7.9), and it might be better if min_distances becomes a temporary table.

Also: this solution does not fetch NULL values for hours without any related records (plus/minus 30 minutes).


Best practices for service name / aliases in tnsnames

I certainly wouldn't want to include the server name in the TNS alias.  It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers.

Assuming that your service names are chosen meaningfully, I would expect that the service name would match the TNS alias since both are logical names for the same thing.  That's not a hard and fast rule, of course.  Some organizations may have reasons to have two separate logical names for a single service.  For example, you may want a single TNS alias FINANCE that points to the FINUSA service for users in the US and the FINFRA service for users in France.  But for the vast majority of situations, if the TNS alias would be FINANCE, the service name ought to be FINANCE as well.


postgres Poor performance on ORDER BY "id" DESC LIMIT 1

Trying to explain why there is difference in performance between the two queries.

This one: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1 is satisfied by any one row with the matching object_id, so the index on object_id is a natural choice. The query requires minimal I/O: index scan to find the first matching value plus one heap read to fetch the entire row.

The alternative: SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1 requires all rows with the matching object_id be sorted by another column, id, then the row with the maximum value of id be returned. If you were to use the index on object_id you would need to perform the following operations: scan the index to find every matching object_id; for every match go fetch the actual row; then sort all fetched rows by id and return the one with the largest id.

The alternative chosen by the optimizer, presumably based on the object_id histogram, is: scan the index on id backwards, in its entirety; for each value go fetch the row and check if the value of object_id matches; return the first matching row, which will have the maximum possible id value. This alternative avoids sorting the rows, so I guess the optimizer prefers it to using the index on object_id.

The presence of an index on (object_id asc, id desc) allows for yet another alternative: scan this new index for the first entry matching the supplied object_id value, which by definition will have the highest id value; go fetch one matching row and return. Obviously, this is the most efficient approach.


Application queries are not getting generated in trace file in oracle

There are a couple of ways of starting a trace in another session.

You can either use a login trigger that sets tracing in the session when it first logs on:

CREATE OR REPLACE TRIGGER TRACE_MY_JAVA
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'MYJAVAUSER' -- edit this condition to pick out your Java sessions
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Or, if the session has already started, you can use dbms_system.set_sql_trace_in_session. Get the other session from v$session:

SELECT sid, serial#
FROM v$session
WHERE -- whatever you need to identify your Java application;

Then execute the proc:

execute dbms_system.set_sql_trace_in_session (sid, serial#, TRUE);

(Add the sid and serial# from the above query)


It looks like for your final result set you want one row for each distinct value of clientname in @NumOfLeads and @PurchasedLicenses. If the client is in both tables then populate the numoflicensesrequested and numlicensespurchased columns. If not then populate the one column that you have. To me, that business logic sounds straightforward enough that you don't need to split it up into multiple queries. Sample data:

CREATE TABLE #NumOfLeads
(
  clientname varchar(500)
, numoflicensesrequested int
, calldate date
, PRIMARY KEY (clientname)
);

CREATE TABLE #PurchasedLicenses
(
  clientname varchar(500)
, numoflicensespurchased int
, purchasedate date
, PRIMARY KEY (clientname)
)

Insert Into #NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03');

Insert Into #PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22');

This query returns the same results as yours:

SELECT
  nol.clientname
, nol.numoflicensesrequested
, COALESCE(pl.numoflicensespurchased, 0) AS numoflicensespurchased
FROM #NumOfLeads nol
LEFT OUTER JOIN #PurchasedLicenses pl ON nol.clientname = pl.clientname
    AND pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
WHERE nol.calldate BETWEEN '2017-01-01' AND '2017-01-31'

UNION ALL

SELECT
  pl.clientname
, 0
, pl.numoflicensespurchased
FROM #PurchasedLicenses pl
WHERE pl.purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
AND NOT EXISTS (
    SELECT 1
    FROM #NumOfLeads nol
    WHERE nol.clientname = pl.clientname
)
order by clientname asc;

I believe that the query could also be written with a FULL OUTER JOIN, but I'm not personally a fan of that syntax.

A few things worth mentioning:

Don't use table variables unless you need the functionality that they provide. Table variables don't have statistics so they can cause poor query performance if you use them in the wrong way. Personally, I only consider table variables if I need to persist data through a transaction rollback or if I have code that will run thousands of times per second (or more).

The fact that you're updating by clientname implies that it's the primary key of the data. Why not define it as the primary key?

For complex queries you can see performance gains by splitting them up into multiple steps by saving intermediate results to temp tables. I would only try that if I couldn't get good enough performance with a single query. For this data and business logic I think that you can just write a single SELECT query. For other queries which are too complex, try doing inserts into temp tables. That will give you minimal logging and other benefits. Avoid doing updates (when practical), and avoid using MERGE unless you really need its functionality.


SQL Server Virtualization best practices - NUMA, HT, Cores vs Speed

The best thing you could do is read the VMWare guide "SQL Server on VMWare Best Practices Guide". This holds all the information you need. However...

Unless the instance is configured as a "wide VM", taking vCores from each socket, then you won't see 2 NUMA nodes. With Hyperthreading enabled, then you're only using the cores on one socket. (3.3.4 NUMA Consideration).
From the best practices guide: "VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each." (3.3.5 Cores per Socket). Test this yourself though.
Where I'm working, the answer is "it depends". Some instances we leave it enabled, and some we don't. However... "VMware recommends enabling Hyper-threading in the BIOS so that ESXi can take advantage of this technology." (3.3.3 Hyper-threading)
If there are going to be no other instances on the host, then it's not as big a factor as if there's going to be more than one instance. It would be of benefit to have those allocated to separate sockets if you're looking to maximise performance. Unfortunately, you'd need to do quite a lot of testing and data capture to make an objective determination - I haven't found anything from VMWare specifically relating to this.

Go through the entire guide and read it: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf.

Other considerations are checking power schemes, because these can throttle CPUs so low that you get very poor performance, even from the best CPUs you can afford.


If you have a composite clustered index on (at least) (column1, column2, column3), then a non-clustered index on column5 is covering for ( column1, column2, column3, column5 ) and would be narrower than the clustered index, especially on a wide fact.  So that might be why.

Can you post the DDL and indexes for your table please?

I'm just going to reproduce that section from "Query Tuning and Optimization", CH3 here as hopefully it will help clear up what looks like a misunderstanding you have about covering indexes:

CREATE TABLE T_heap (a int, b int, c int, d int, e int, f int)
CREATE INDEX T_heap_a ON T_heap (a)
CREATE INDEX T_heap_bc ON T_heap (b, c)
CREATE INDEX T_heap_d ON T_heap (d) INCLUDE (e)
CREATE UNIQUE INDEX T_heap_f ON T_heap (f)
CREATE TABLE T_clu (a int, b int, c int, d int, e int, f int)
CREATE UNIQUE CLUSTERED INDEX T_clu_a ON T_clu (a)
CREATE INDEX T_clu_b ON T_clu (b)
CREATE INDEX T_clu_ac ON T_clu (a, c)
CREATE INDEX T_clu_d ON T_clu (d) INCLUDE (e)
CREATE UNIQUE INDEX T_clu_f ON T_clu (f)

Hopefully that makes senses!


Expand sparse table with self outer join on distinct values

I don't know if this is any more straightforward, but I would change the syntax to use ANSI JOIN syntax instead of the (+)

select t1.g1,
  t1.g2,
  t2.x
from
(
  select distinct t1.g1, t2.g2
  from yourtable t1
  cross join (select g2 from yourtable) t2
) t1
left join yourtable t2
  on t1.g1 = t2.g1
  and t1.g2 = t2.g2
order by t1.g1, t1.g2

See SQL Fiddle with Demo


Node and Instance Relationship on Oracle RAC

In general, it would mean that the instance that was servicing the request failed and is no longer running.  In a RAC cluster, you run one instance on each node of the cluster.  I wouldn't make sense to move an instance from one node to another in the cluster-- that would imply that one node would be running two instances of the same database.  Depending on your environment, the DBA might choose to add a new node (and a new instance) to the RAC cluster at least temporarily in response to the failure of one of the instances.


Can we have more than one arbiter in monogodb replica set

There is no need in having 2 arbiters.

The minimum recommended replica set consists of THREE servers and is:

a primary
a secondary
an arbiter

the arbiter can be on either machines, but it is recommended to be on a separate machine (VM or anything) so that it won't go down with one of the servers if something goes wrong.

You could use multiple arbiters but if you have only two machines, it will do you no good because you always need n/2 + 1 servers for a succesful election.


Why MySQL performs full table scan if subquery is used in update statement

IN ( SELECT ... ) has always been poorly optimized in MySQL.  There were improvements in 5.6, but it does not seem to cover your case.  File a bug with http://bugs.mysql.com if you like.

It is often better to change to a JOIN since it optimizes better.  (I assume your real query is more involved, and you simplified it for this Forum.)

You can verify whether it does a table scan:

FLUSH STATUS;
perform the operation
SHOW SESSION STATUS LIKE 'Handler%';

Meanwhile, the "rows" in EXPLAIN is pessimistic in some situations.  I would trust the Handler counts when in doubt.  With 15 rows instead of 10, I got (excluding 0 values):

| Handler_commit             | 1     |
| Handler_external_lock      | 4     |
| Handler_read_first         | 1     |
| Handler_read_key           | 18    |
| Handler_read_rnd_next      | 16    |
| Handler_update             | 2     |
| Handler_write              | 2     |

That smells like a tmp table with 2 rows, plus a full scan (maybe twice?) of 15 rows, plus some other miscellany.


How to display User Mapping for offline databases?

You can use sys.databases joined with sys.server_principals

SELECT sd.NAME AS databaseName
    ,sp.NAME AS database_owner
FROM sys.databases sd
INNER JOIN sys.server_principals sp ON sd.owner_sid = sp.SID
WHERE sd.state_desc = 'OFFLINE' --> since you want for offline databases !

You can even use at your own risk --> sp_msloginmappings ( <-- Undocumented and unsupported :-) )


SSIS Visual Studio 2008 reference variable scope in expression builder

No, the availability of variables is always subject to the scope hierarchy.

If you can't access the variable you want, then you need to refactor the structure or redefine the variable at a higher/different scope so that it's available. Sometimes this issue is indicative of a design problem in the package structure itself.


which process has created a load spike on my server last week?

If you have the slow query log active, you might be able to identify a long running query during the timeframe of your load spike. This probably the only way to figure out the issue that has already occurred.

You can configure a script like pt-stalk to try to capture future issues, however.

Here's an example to trigger data collection if you have more than 10 connections from host '10.0.0.23' [src]:

pt-stalk --function processlist --variable Host\
 --match 10.0.0.23 --threshold 10 -- --defaults-file=./my.default.cnf


Maintenance Plan Wizard generates invalid script?

The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:

File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files.  The path must end with a backslash "\".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:


Automatically execute query in MS SQL Studio every hour

Your friend is sqlcmd (Microsoft Technet)

Create a SQL file with the script required to run your cleanup job
Run the script with sqlcmd.exe and any required parameters
Create a Windows Scheduled Task and add the command with all the required parameters

E.g.

sqlcmd -d YOUR_DB -E -i YOUR_SCRIPT.SQL -o OUTPUTFILE.TXT

Good luck.


How do I convert the sum of minutes into Hours:Minutes format

You'll simply have to build that format manually.

SELECT  t.TravelTime, t.TotalProductiveTime,
        TotalServiceTime = CONVERT( NVARCHAR( 16 ),
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) / 60.0 ) )
            + N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER,
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) % 60.0 ) ) )
FROM (  SELECT  TravelTime = CONVERT( MONEY, 2520.00 ),
                TotalProductiveTime = CONVERT( MONEY, 10240.00 ) ) t

The above calculation divides the total number of minutes by 60 to get the number of hours, then uses the modulo operator (%) to return the remaining number of seconds.  The results are appended together to form the hours:minutes string desired.

To put this into the context of your query, you could put your T-SQL into a CTE or subquery, then perform the calculation above on the two components:

;WITH src AS (
    SELECT (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type
          ON action.act_type_id = dbo.act_type.act_type_id
      WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Travel Time], (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type AS act_type_6
          ON action.act_type_id = act_type_6.act_type_id
      WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Total Productive Time]
    FROM dbo.incident AS inc
    INNER JOIN dbo.assyst_usr
        ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
    INNER JOIN dbo.serv_dept
        ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id
)
SELECT src.[Travel Time]
    , src.[Total Productive Time]
    , [Total Service Time] = CONVERT(VARCHAR(20)
        , FLOOR((src.[Travel Time] + src.[Total Productive Time]) / 60))
        + ':'
        + CONVERT(VARCHAR(20),
             (src.[Travel Time] + src.[Total Productive Time]) % 60
          )
FROM src ;


SQL Server AG secondary replica database still in Restoring mode

I found the solution. It is because my account doesn't have sufficient permission.


Postgres not using indexes when ORing across join

It's sometimes useful to rewrite a query with OR conditions as a UNION (ALL) query. This usually leads to a different execution plan which may be more efficient. Testing will identify which of the two methods/plans is more efficient in each case (eg. if the trigram indexes are used for both parts of the union).

The rewrite:

SELECT *
FROM "tableA" AS a
    LEFT OUTER JOIN "tableB" AS b
    ON a."tableB_id" = b."id"
WHERE a."name" ILIKE '%rich%'

UNION ALL

SELECT *
FROM "tableA" AS a
    INNER JOIN "tableB" AS b
    ON a."tableB_id" = b."id"
WHERE b."name" ILIKE '%rich%'
  AND (a."name" IS NULL OR a."name" NOT ILIKE '%rich%')
 ;


Compatibility mode change does not reflect on secondaries

Below

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

is not propagated to secondaries in DB mirroring or AlwaysON since they ship log blocks vs transactions - (logshipping you take log backups and ship it to secondary server and restore them).

There is a reason why they should not change the DB compatibility level as you certify your application based on DB Compatibility level vs a particuliar version of sql server.

This is same as if you change the owner of database .. you have to change it on secondary when it becomes Primary.

  how can i really get this changed without breaking LS or mirroring if possible.

You dont have to break mirroring or LS. You have to just failover and failback.

For logshipping, since it is physical log backup-copy-restore, once you restore the database and bring it online or readonly it will reflect the change. For mirroring, you can just failover and failback.

The point is to bring the db to read/write state and do alter database in AG or mirroring configuration.


Configure pgAdmin->Paths->Binary paths and set "PostgreSQL Binary Path" as shown in the screenshot.  Depending on your OS and installation details, the binaries may be located elsewhere.  Try

`which psql`

from the command line on linux/Unix systems.

A Windows example:
PostgreSQL Binary Path: "C:\Program Files\PostgreSQL\9.6\bin"


Is there any way to get the position of a value in a table returned by a SELECT statement?

There is a ROW_NUMBER() analytic function but I don't think it's available in SQLite. So, here's a generic approach (which may be quite slow):

SELECT pos - 1 AS ROWNUMBER     --- the "-1" is to get the C-style row numbering
FROM
  ( SELECT b.id
         , COUNT(*) AS pos
    FROM myTable AS a
      JOIN myTable AS b
        ON a.id <= b.id
    WHERE a.aCondition = 1
      AND b.aCondition = 1
      AND a.id <= 4             --- optional for
      AND b.id <= 4             --- performace
    GROUP BY b.id
  ) AS tmp
WHERE id = 4

Note that you should not use the additional two lines (checks), if your final WHERE checks for something else than id.

Rethinking, there's an even better way, because you only want the Row_Number of the last element in that list. There is no need to number them all, we can just count them:

SELECT CASE WHEN maxid = 4     --- so we don't get a RowNumber for an @id
              THEN cnt-1       --- that does not meet the condition
              ELSE NULL        --- or is not in the table at all
       END AS ROWNUMBER
FROM
  ( SELECT MAX(id)  AS maxid
         , COUNT(*) AS cnt
    FROM myTable AS a
    WHERE a.aCondition = 1
      AND a.id <= 4
  ) AS tmp


Why does VALUES provide a different DEFAULT column name than SELECT?

The general approach of the SQL standard is that many things not imposed by chosen semantics are left to the vendor to optimize or specify. Under the chosen policy users choose names when they care what they are.

SELECT without FROM isn't standard SQL either. MySQL chooses to use the expression that evaluated to a column's value as its name. Those PostgreSQL "?column?"s aren't the column names, they are strings output instead of the column names. If the system defaults to internal unique names then clashes are avoided compared to defaulting to patterned names. In your example you could have selected "column1" but with your expected behaviour you'd need "t2.column1".

Ultimately, they just chose a certain design for the language. And even where people leave a rationale, engineering and ergonomics are about pragmatic timely tradeoffs where one decision cannot necessarily be justified as "best".


Solved the issue. I'm not sure what the cause was, but this was an error that was specific to me using doing TLS and couch in a VM. I spun up a VPS and tried it there and the problem was completely gone.


Is there any value in adding an already indexed column to a composite index?

The query

select col1,col2
from mytable
where
  col1 between a1 and b1
  and col2 between a2 and b2

can be answered using index3 without accessing the table.

If there is only index1 and index2 for example the following methods are possible:

method I:
1) check for each row from mytable if col1 between a1 and b1 and col2 between a2 and b2
method II:
1) find all rows with col1 between a1 and b1 by using index1
2) lookup each row in mytable and check if the col2 between a2 and b2
method  III:
1) find all rows with col1 between a1 and b1 by using index1
2) find all rows with col2 between a2 and b2 by using index2
3) find the intersection of the rows found by step 1 and step2

All of these methods are inferior to using index3:

Method I needs to scan the whole table even if there are only a view (or no) rows that satisfy the condition.

Method II can be even worse than method I if step 1 returns a lot of possible candidates that must be looked up.

In Method III both step 1 and step 2 can return a lot of rows but the intersections is only a small number of rows.


SQL Server 2008 R2 - Specify the Full Backup to use when doing a Differential Backup

You should do the daily backups as copy_only, so that it doesn't break the chain for transaction log backups or differential backups. After that the daily full backup won't be taken into account as basis for the differential backups.

Quote from MSDN:

"COPY_ONLY
    Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base."


Performance Issue after Truncate and Load of a table in Oracle

Loading Truncated Table

Assume staging table is X_SOURCE, and target table to load is X_TARGET, and any calculations performed when loading X_TARGET can be done via SQL functions or user-defined functions.  Here we load col_a as is, inserting current date into col_b, col_c is multiplied by 2 times, and col_d as is.

alter table x_target nologging;
alter session enable parallel dml;
-- possibly drop indexes

insert /*+ append parallel(6) */ into X_TARGET
   (select col_a, sysdate, col_c * 2, col_d from X_SOURCE);
-- recreate indexes if they were dropped
alter table x_target logging;

-- then invoke RMAN commands to back up the table

The above has the following features:

Turn off logging on the target table x_target to avoid logging
overhead
The /*+ append */ hint appends data to the end of the table
and bypasses several steps done during normal inserts
Dropping and recreating the indexes can be much quicker than doing index
maintenance during the load.
Don't forget to turn logging back on
Ensure a backup is done after the load

New Server Issues

If the new server has more CPU/memory/etc. and you are seeing longer run time, I would definitely run the DBMS_STATS.GATHER_SYSTEM_STATS('START', ...) procedure, (after creating a table to store the stats being gathered), run your truncate/load process, then run DBMS_STATS.GATHER_SYSTEM_STATS('STOP')  and then query your stats table, and use DBMS_STATS.SET_SYSTEM_STATS to set values that need to be adusted -- see Performance Tuning Guide for details.  If you are on Oracle 12, also look at DBMS_STATS.GATHER_PROCESSING_RATE -- run it too while your process is running.


MySQL Replication Error, can't stop slave

Which version of MySQL you have installed in your slave server?

This behaviour seems to be a bug fixed starting from MySQL 5.5.0

see MySQL 5.5.0 Changelog

in:

  Important Change: Replication: MyISAM transactions replicated to a
  transactional slave left the slave in an unstable condition. This was
  due to the fact that, when replicating from a nontransactional storage
  engine to a transactional engine with autocommit disabled, no BEGIN
  and COMMIT statements were written to the binary log; thus, on the
  slave, a never-ending transaction was started.

  The fix for this issue includes enforcing autocommit mode on the slave
  by replicating all autocommit=1 statements from the master. (Bug
  29288)

Bug details here: http://bugs.mysql.com/bug.php?id=29288

If it is, "simply" before upgrade your slave server, then you can plan an upgrade of the master.


Need parentheses in the WHEN part:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 (NEW.connection_root IS NULL)
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

You also need to return NEW in your trigger function.

create or replace function  dionaea.connections_INSERT_update_connection_root_trg() returns trigger language plpgsql as $$
begin
  update dionaea.connections set connection_root = connection where connection = new.connection and new.connection_root is null;
  return new;
end $$;


Processor queue length but not very high CPU usage

While the recommendations below may not specifically reduce the processor queue length below 4 on a 4-CPU VM, they will help with overall performance, which is presumably why you're asking your question.

Best practices published by VMware and Microsoft recommend explicitly that you should set both a CPU reservation, and a memory reservation for any VM running SQL Server.

See Architecting Microsoft SQL Server on vSphere - Best Practices Guide by VMware for all the details about how to make SQL Server run reliably with the best performance possible.

I know you're not using Microsoft's Hyper-V product, but for future users who might see this post, see Running SQL Server with Hyper-V Dynamic Memory for their recommendations.

Specific recommendations the get the best performance out of SQL Server include:

Configure "max server memory" to a setting that makes sense for your environment.  This post may help you determine what that should be set to.
Ensure you set "min server memory" to a value that makes sense.  For production SQL Servers, it is "typical" to set this either to the same value as "max server memory", or to a value 1 or 2 GB lower than max server memory, in case the O/S experiences extreme memory pressure.
Configure the service account used by SQL Server with the "Lock Pages in Memory" right.  See this post for details.
Ensure you have at least 2 cores configured for the VM.  You probably want many more than 2, but setting up the VM with a single core is a recipe for disaster.  For production SQL Server installs, I'd recommend at least 8 cores.
In Hyper-V, configure the VM to be NUMA aware, and disable "Dynamic Memory".  Many server folks will balk at this suggestion since it reduces the number of VMs that can be ran on any single host server; however it is critical for good performance for any service that consistently needs a lot of memory, such as a database management system.  In VMWare, configure the VM with a memory reservation that matches the total amount of memory assigned to the VM.  This prevents the VMware balloon driver from consuming memory in the VM, which would in turn reduce memory available to SQL Server, and thereby reduce performance.
Ensure you use configure the VM with a high-queue SCSI interface and network interface - read the best practices docs linked above for specific recommendations.

As @SqlWorldWide mentioned in a comment on your question, you should look at this.  Taken from that newsgroup post:

  First of all anything below 10 is normal for the processor queue length
  counter in the perfmon (according to the perfmon's documentation)

  Secondly if you have multiple processors you have to divide the queue length
  by the number of the processors. Because there is only one scheduler in the
  SMP system too.

  And lastly the queue length is an instanteneous counter. It shows the number
  of ready threads right now, which is a sort of misnomer because IMHO it
  takes into consideration only the highest priority queue (note that there is
  queue per priority). Those threads will eventually block on something like
  I/O or synchronization mechanism.

  Now the CPU usage counter is different:  It is calculated by
  measuring the duration of the idle thread is active in the sample
  interval, and subtracting that time from interval duration  Idle
  thread will run only when there is no ready thread with higher priority. And
  this is essentially the basic assumption of any preemtive system. I can have
  single thread (in addition to the Idle one) but if it runs code like this:

  while ( true );

  The CPU usage counter will show 100%. Note that in this hypothetical case
  the queue length is 1 :o)


MySQL FULLTEXT search not returning any results

First, ignore the key_len of explain, that value only has a meaning in the context of a BTREE index, so it will always be 0 for a FULLTEXT scan, regardless of the actual size of the index/column.

Second, by default, the FULLTEXT index is used in "natural language mode", which, as it is documented, will ignore results that happen in 50% or more of the rows ("words that are present in 50% or more of the rows are considered common and do not match."). As you only have 1 row, you will always get 0 results. Note that if you add 2 more non-matching records, you will get the desired results:

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('blabbity') AS 'relevance'
       FROM
       `searchable_content`  WHERE
       MATCH(`content`) AGAINST ('blabbity');
+---------+--------------------+
| page_id | relevance          |
+---------+--------------------+
|      42 | 0.3573872447013855 |
+---------+--------------------+
1 row in set (0.00 sec)

If you may want to get all results, regardless of the percentage of matches, I recommend you using the boolean mode:

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE) AS 'relevance'
       FROM
       `searchable_content`
       WHERE MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE);
+---------+-----------+
| page_id | relevance |
+---------+-----------+
|      42 |         1 |
+---------+-----------+
1 row in set (0.00 sec)

Although you lose the ranking. Be careful, as you will never match words shorter than ft_min_word_len (innodb_ft_min_token_size for InnoDB). Changing this variable may require recreate the index.

Also be aware that you could use a single index to search on the three columns at the same time.

Last but not least, fulltext search is available for InnoDB starting with MySQL 5.6, and both 5.6 and 5.7 bring some interesting improvements in terms of performance. If you need a more flexible, feature-complete or performant search solution, you have to go to an external tool like Sphinx or Lucene.


How can i prevent decryption in SQL Server procedures

SQL Server does not actually encrypt the definitions of objects, it obfuscates them instead. It is a bit misleading I am afraid as all it is really doing is making it unreadable to humans [excluding the use of tools as you previously mentioned].

Also SQL Server needs to know the contents of the procedures, functions & views for when it needs to recompile the execution plan etc... so real encryption would prevent SQL Server from being able to do this. As a result this is not possible.

My best recommendation if you are worried about IP & copyright is to secure your software with Licensing. You may not be able to prevent someone from reverse engineering your code but you can sue the pants off them if they do.


Manually specifying candidate masters in Maxscale

You can use priorities as described here: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-galera-monitor/


Loop inside of query in mysql procedure. Is it possible?

I solved this writhing the following code:

drop procedure if exists populate;
delimiter $$
CREATE PROCEDURE populate (IN n INT, IN l INT, IN k INT)
BEGIN
  declare _k int unsigned DEFAULT 0;
  declare _n int unsigned DEFAULT 0;
  declare _l int unsigned DEFAULT 0;
  DECLARE _stmt TEXT DEFAULT 'INSERT INTO major_1 (';

  WHILE _n < n DO set _n=_n+1;
    set _stmt = CONCAT(_stmt,'minor_',_n,'_id');
    IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
  END WHILE;
  set _stmt = CONCAT(_stmt,') VALUES ');

  while _k < k do set _k=_k+1;
    set _stmt = CONCAT(_stmt,'(');
    set _n=0; WHILE _n < n do set _n=_n+1;
      SET _stmt = CONCAT(_stmt,CEIL(RAND()*l));
      IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
    END WHILE;
    IF _k<k THEN set _stmt = CONCAT(_stmt,'),');
    ELSE set _stmt = CONCAT(_stmt,');'); END IF;
  end while;

# code to debug:
# SELECT n,l,k,_stmt;

  SET @STMT = _stmt;
  PREPARE stmt FROM @stmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
delimiter ;

And this can be used (for N=20,L=25,K=3) by command:

CALL populate(20,25,3);

Sources:

Loops in MySQL

  https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql

Execution of text variable as query

  https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure


sp_WhoIsActive and get_plans -- Actual Plan?

The tool grabs the plan that is being run at that time. From an email response from Adam:

"Actual plan" usually refers to the query plan that has the actual row counts, number of executions of each iterator, etc. Who is Active cannot return that version of the plan. But the plan it returns is indeed the "actual" plan that's running at the time -- in other words, the plan will not be recompiled into some other plan by the time Who is Active can get it.


What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?

With dbvisit you can maintain a standby database, in a very similar way as Data Guard does in the Enterprise Edition. You might want to check this out. The setup is very easy, support is good and the pricing is very reasonable, if not, cheap.
I blogged about it a few times,

see how to configure dbvisit to manage existing standby database running SE

and Cost reduction using Oracle SE and dbvisit standby

I hope this helps,
Ronald.


I am glad you mentioned the table was MyISAM.

You can now run this

OPTIMIZE TABLE mytable;

on the 14.8G table.

So why did you get #1030 - Got error 28 from storage engine ?

It wasn't so much the indexes in themselves. Sometimes, a temp table may need to be written to disk. If there is no space for the temp table on disk, mysqld will not crash. It will freeze until space becomes available. I wrote about this before:

Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop
Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

If you want to compress every table regardless of storage engine, run this code in a script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/CompressTables.sql
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN')"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/CompressTables.sql
less /tmp/CompressTables.sql

If the file looks good to you, run this

mysql ${MYSQL_CONN} -A < /tmp/CompressTables.sql

If all your tables are huge, run this over the weekend


Need to choose ASM performance for production database

ASM is not magic.

Each disk has a maximum number of IOPS it can support.  Each disk has a maximum throughput.  The more spindles (HDDs) you have, the more I/O you can get.  If one disk decides to take a permanent vacation and you don't have data on another disk.... I really hope you have viable tested backups.

From my tests, partitioning a single HDD into multiple LUNs and then using those LUNs for ASM will have a negative effect (albeit, ~-1%).

If you want performance, you will buy 6+ 500GB HDD (or SSD).  Each disk will have 1 LUN - the whole thing.  ASM will take care of the stripe and mirror.


It is because you are declaring your @test variable as nvarchar(max) and comparing that to a non lob literal in the OPTIMIZE FOR clause.

From the docs on OPTIMIZE FOR :

OPTIMIZE FOR ( _@variable\_name_ { UNKNOWN | = literal_constant } [ , ...n ] )

You should change the length of the variable  to the size of the highest possible character length of the value that will be stored in it.

DECLARE @test nvarchar(3);

DB<>Fiddle

NVARCHAR(MAX)  and OPTIMIZE FOR

In theory you are able to optimize for nvarchar(max) fields, but it appears that the literal has to be over 4000 (for nvarchar) characters long:

When creating 4000 spaces:

SELECT REPLICATE(' ', 4000);

And adding them to the end of the constant:

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test  = N'one                                                                                ' ))

It works too.

You cannot use CAST() or CONVERT() to explicitly change the constant to NVARCHAR(MAX)
DB<>Fiddle

Extra

If the variable is lower than nvarchar(max) but the constant is over 4000 (for nvarchar) characters, it still works.:

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(3)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test  = N'one                                                                                ' ))

Because all the spaces after the literal value are 'cut off' and it is treated as a nvarchar(3):

<ColumnReference Column="@test" ParameterCompiledValue="N'one'" ParameterRuntimeValue="N'one'" />

If we change the variable to nvarchar(4)

DECLARE @test nvarchar(4)

One more space is present in the ParameterCompiledValue

<ColumnReference Column="@test" ParameterCompiledValue="N'one '" ParameterRuntimeValue="N'one'" />


Danke schön to @a_horse_with_no_name for pointing EXPLAIN ANALYZE VERBOSE that shed light on a foreign key loop in my definitions.

I need to explain more about the tables:

An item is composed of parts p1, p2, ... (12 or 13 types) and of "meta parts" meta_p1 that are groups of several parts p1
A device is like an item (it is also composed of parts and meta part 1) but with different columns.

Which means we have a graph like this (mind the ASCII-art ;))

 DEVICE       ITEM
(1)(1)       (1)(1)
 |   \       /   |
  \  (n)   (n)  /
   \  META_P1  /
    \   (1)   /
     \   |   /
     (n)(n)(n)
         P1

(I might have reversed (1) and (n), I mean "(1) item has (n) meta_p1").

Whenever an item is deleted, I want all parts p1, p2, ... and meta_p1 to be deleted as well. Same when deleting a meta_p1: all p1 "contained" in the meta part were supposed to be deleted (which is a logic flaw, as an instance of p1 can also exist outside of meta_p1). It meant that there would be a kind of n^2 search because of that loop (delete item => delete p1 and meta_p1 but delete meta_p1 also means delete p1), as shown on the EXPLAIN ANALYZE VERBOSE results:

Delete on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.100..0.100 rows=0 loops=1)
  ->  Index Scan using "item_pkey" on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.040..0.041 rows=1 loops=1)
        Output: ctid
        Index Cond: (item._id_ = 29919)
Trigger RI_ConstraintTrigger_a_24686 for constraint fk_item on item: time=0.116 calls=1
Trigger RI_ConstraintTrigger_a_24719 for constraint fk_item on item: time=0.232 calls=1
Trigger RI_ConstraintTrigger_a_24747 for constraint fk_item on item: time=33.225 calls=1
Trigger RI_ConstraintTrigger_a_24784 for constraint fk_item on item: time=50.434 calls=1
Trigger RI_ConstraintTrigger_a_24817 for constraint fk_item on item: time=0.351 calls=1
Trigger RI_ConstraintTrigger_a_24840 for constraint fk_item on item: time=0.333 calls=1
Trigger RI_ConstraintTrigger_a_24863 for constraint fk_item on item: time=0.278 calls=1
Trigger RI_ConstraintTrigger_a_24886 for constraint fk_item on item: time=0.269 calls=1
Trigger RI_ConstraintTrigger_a_24909 for constraint fk_item on item: time=0.318 calls=1
Trigger RI_ConstraintTrigger_a_24932 for constraint fk_item on item: time=0.142 calls=1
Trigger RI_ConstraintTrigger_a_24950 for constraint fk_item on item: time=16.547 calls=1
Trigger RI_ConstraintTrigger_a_24977 for constraint fk_item on item: time=48.029 calls=1
Trigger RI_ConstraintTrigger_a_25003 for constraint fk_item on item: time=0.182 calls=1
Trigger RI_ConstraintTrigger_a_24769 for constraint fk_p1 on meta_p1: time=1285.033 calls=53
Trigger RI_ConstraintTrigger_a_24789 for constraint fk_dev on meta_p1: time=102571647.818 calls=53
Total runtime: 102573083.569 ms

The constraint fk_item on item are one for each p* table, and the last two lines show the loop (though I don't understand what fk_dev is doing here as I'm not considering "devices" but "parts").

(Table definitions were not included for brevity).


Why do you use the    -cluster - option ?
Is you database repository part of a cluster ?

Make sure first that you can connect to the database using the tns names.

Example :

sqlplus system/password@'tns_identified'

Then to deconfig you em repo :
Try using this syntax :

 emca -deconfig dbcontrol db -repos drop -SYS_PWD oracle -SYSMAN_PWD oracle

For some nice articles on how you can prepare you Oracle Enterprise Grid Control Monitoring environment check the link


Often times slow queries are caused by a lack of index on a column that is being used in a where or join clause.

Try indexing all of the following:

u.user_id
f.user_id
f.main
u.mode
u.activated

You could also try moving f.main='Y' to the WHERE condition - I believe it will result in the same outcome.


I have to update all the rows of a Column in a table. Here is my query

Try this:

UPDATE tracks t
INNER JOIN top100 t1 ON t.track_title = t1.track_title
LEFT JOIN People p ON t1.artist = p.People_name
SET t.People_id_Reference = p.People_id
WHERE t.People_id_Reference IS NULL;


MySQL: grant SELECT to all databases except system

may i correct what you have posted above..

"create user 'reader' identified by 'password';
grant select on . to 'reader';"

try below

create user reader@whatEverIPAddressOfUser identified by 'password';
grant select on . to reader@whatEverIPAddressOfUser;
flush privileges;

then issue your revoke statement

revoke SELECT ON mysql.* from reader@whatEverIPAddressOfUser;
flush privileges;


I've rid of the redundant second subselect so now it is the single one that fetch employee(s) having earliest date of birth (highest age), their IDs, names, IDs of their managers and names of managers.

SELECT ds.dept_no
     , ol.emp_no     AS oldest_emp_no
     , ol.birth_date AS oldest_bd
     , e1.first_name AS o_first_name
     , e1.last_name  AS o_last_name
     , dm.emp_no     AS req_manager_no
     , e2.first_name AS m_first_name
     , e2.last_name  AS m_last_name

  FROM departments  AS ds
  JOIN dept_emp     AS de ON de.dept_no = ds.dept_no
  JOIN employees    AS e1 ON e1.emp_no = de.emp_no
  JOIN ( SELECT MIN(birth_date) AS birth_date FROM employees )
         AS ol ON ol.birth_date = e1.birth_date
  JOIN dept_manager AS dm ON dm.dept_no = ds.dept_no
  JOIN employees    AS e2 ON e2.emp_no = dm.emp_no
;


Amazon RDS instance recovery during mysqldump

This is a confirmed bug with Amazon RDS.

Reply from Amazon on the 6th Jan 2020:
"Starting with MariaDB 10.2.2 resizing the buffer pool is performed in chunks determined by the size of the innodb_buffer_pool_chunk_size variable. Hence the buffer pool size must be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, since innodb_buffer_pool_size is set to 1.5 GB the behavior is to round off to 1GB as the "innodb_buffer_pool_instances" is set to 8."

Original Answer:
On the "db.t2.small" MariaDB instance, it has 2GB of RAM, but innodb_buffer_pool_size is being incorrectly set to 2GB.
This means MariaDB will crash when the InnoDB buffer pool fills with too much data.
The temporary solution is to change the Parameter Group so:
innodb_buffer_pool_instances = 12

If you want to get an ETA from Amazon, ask them to check Case 6411919871, from the 5th September 2019.
I did have to eventually pay them $29 to raise a "Technical" bug report, the original report was raised in August, both with Accounts, and on their Forum:
https://forums.aws.amazon.com/thread.jspa?messageID=913625


How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus?

I discovered that collections must have the same data type for an assignment to work. Having the same element type is not enough.

Armed with this information, I set out to figure out what type I needed to use:

SQL> select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = '
PACKAGE' AND OWNER='ASPNETDB' order by OWNER, OBJECT_NAME;

OBJECT_NAME                    OWNER
------------------------------ ------------------------------
ORA_ASPNET_PROF_PKG            ASPNETDB
ORA_ASPNET_ROLES_PKG           ASPNETDB

select line, text from user_source where TYPE = 'PACKAGE'; returned a bunch of garbage which included a line having: 1ASSOCARRAYVARCHAR2_T.  That prompted me to try changing my two variables to:

usernames ora_aspnet_roles_pkg.assocarrayvarchar2_t;
rolenames ora_aspnet_roles_pkg.assocarrayvarchar2_t;

That is the correct solution to my problem.  If someone can suggest a less roundabout way of finding what type to use inside a package, I'll gladly accept that answer.


What does "+date+" mean in this context?

SELECT LTRIM(RTRIM(CONVERT(char(10), MAX(date), 101))) as str
FROM   inventory
WHERE  date < '" + date + "'"

  Table: inventory

  Field: date (Your inventory table has a field named date)

MAX(date): Returns the highest date according WHERE conditions.

CONVERT(char(10), MAX(date), 101): Convert one data type into another applying some style. On this case 101 means U.S. format: mm/dd/yyyy. This is a MS SQL-Server specific syntax.

RTRIM(): Returns a character string after truncating all trailing spaces.

LTRIM(): Returns a character expression after it removes leading blanks.

Give me the highest date of inventory table, using mm/dd/yyyy format,
without leading and trailing spaces and considering only records
where this date is lower than an specific value.


Get and Put performance testing on Google BigTables (and other integrated DBs)

It seems to me your problem is that you are trying to test performance metrics that are not well supported in the underlying db.  This makes it very difficult to compare performance across systems because the underlying approaches are very different.  I don't think it is possible to do apples to apples comparisons just as I don't think you can do an apples to apples comparison of ORDBMS type approaches to RDBMS type approaches.  The performance concerns are just too different and if Stonebraker is right that optimizing an ORDBMS for TPC-C tests misses the point, then for systems that are even further apart it is going to be impossible. (I think he is right there, however, only where ORDBMS functionality comes into play.)

I think what you need to be honest is to look at how you would use each system and build a benchmark tool based on the approach you'd take with each.  Then you can say at least for that workflow that the benchmark shows something specific.  I don't see how you can generalize however.  You could further run it in a profiler in order to get additional information about where the time is being spent in the test run on various engines.

However db benchmarking is very difficult to make meaningful in the best of circumstances and when you are comparing dissimilar systems it becomes impossible to generalize.


Update all geometry points by adding/subtracting from XY

This makes absolutely no sense, but you would want to do something like this

UPDATE myTable
  SET geoPoint = Point( ST_X(geoPoint)+0.1, ST_Y(geoPoint)-0.1 );

This will move absolutely in the base unit of the SRS.


The answer is to use 1 PRECEDING, not CURRENT ROW -1. So, in your query, use:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID
                            ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND 1 PRECEDING)
    AS  PreviousRunningTotal

Also note that on your other calculation:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID) ...

SQL-Server uses the default * RANGE UNBOUNDED PRECEDING AND CURRENT ROW. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want).

Much more details you can find in the blog article by @Aaron Bertrand, including performance tests: Best approaches for running totals – updated for SQL Server 2012

* this is of course the default range when an ORDER BY is present inside the OVER clause - otherwise, without ORDER BY the default is the whole partition.


Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd

In your command that works, you don't have an equal sign (=).  When you run the command that fails and look at the output, you should notice the equal sign disappears.  That's because that character needs to be escaped with a caret. (in my example below, notice the caret before the equal sign)

Try:

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql" -v dbname^="test"`) DO (
    Echo %%F
)


SQL Server Maintenance plan that goes to next step when the condition passes

As mentioned in the comments you got to refer Ola Hallengren's  maintenance solution.

However, following conditional script might be helpful for any other purpose that need consider only primary replica:

Declare @is_DB_HADREnabled bit;
Declare @isPrimaryReplica bit;
Declare @Databasename varchar (120) = DB_NAME();

select @is_DB_HADREnabled = IIF(group_database_id IS NULL, 0,1) from sys.databases where [name] = @DatabaseName;

        BEGIN
            IF EXISTS (select db.name
                        from sys.dm_hadr_database_replica_states as hadr
                            join sys.databases as db on hadr.group_database_id = db.group_database_id
                        where is_local = 1 and is_primary_replica = 1 and db.name = @DatabaseName
                        )
                SET @isPrimaryReplica = 1
            ELSE
                SET @isPrimaryReplica = 0
        END

    IF @is_DB_HADREnabled = 0 or @isPrimaryReplica = 1

        BEGIN
            Print 'This block able to run on only Primary replica when a database part of AG. Also, run for the databases which are not part of AG'
        END

        ELSE
        PRINT 'This has to be either Primary replica of DATABASE or DATABASE must not be part of any AG'


Here's how I would re-write this. Various comments inline. These changes may help reduce blocking but it's really hard to see how even the original would block for very long unless these are all heaps and/or every table access yields a scan...

DECLARE @min INT, @com INT; -- these are INTs, right?

SELECT TOP 1 @com = companyid
  FROM dbo.tblcompanies -- always use schema prefix
  ORDER BY NEWID(); -- random company? really?

-- Minimize transaction duration. Any real need to select the
-- random company & perform final select inside the transaction?

BEGIN TRANSACTION;
  BEGIN TRY

    SELECT @min = MIN(productid)
      FROM dbo.tblproducts WITH (UPDLOCK)
      WHERE companyid = @com
      AND (lockstatus <> 1 OR lockstatus IS NULL);

    UPDATE dbo.tblproducts
      SET lockStatus = '1' -- is this column numeric or string?
      WHERE productid >= @min AND productid < @min + 4; -- values guaranteed contiguous?

    COMMIT TRANSACTION;

    SELECT -- don't believe DISTINCT or TOP are required here
      s.productname, s.productid,   s.companyid,
      r.Type,        r.requestType, r.UserID
    FROM dbo.tblproducts AS s
    INNER JOIN dbo.tblcompanies AS r
      ON r.companyid = s.companyid
    WHERE s.productid >= @min AND s.productid < @min + 4
    AND EXISTS -- since no columns from rd are needed
    (
      SELECT 1 FROM dbo.tblcompanydetail
      WHERE productid = s.productid
    );

  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
  END CATCH


If you know the time from where you need to start the backup then you can use mysqldump with --where clause like below to get the type of backup you are looking for

mysqldump --user=username --password=password db_name table_name  --where=date_pulled > '2014-07-21 16:25:00' > backup.sql

You can also do like

SELECT * FROM table_name
WHERE date_pulled > '2014-07-21 16:25:00'
INTO OUTFILE 'table_name.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

You can create a simple automated script for this and change the date_pulled dynamically.

Have a look at below links that may help you as well

 Mysqldump in CSV format.

How to output MySQL query results in csv format?


It could be a "cold backup" where someone shuts down the database and copies the PGDATA directory files.  If that is the case, it should be a valid backup.  You can test it by copying the entire directory to some test machine with the same architecture as the database server, taking that test machine off the network (in case it tries to reach out and touch someone when you start it up), and then start it up.  You can also run the program pg_controldata against this directory.  If the database was shutdown at the time the backup was taken, you will get a line like this:

Database cluster state:               shut down

This suggest it was a cold backup and should be valid, but it is not conclusive proof.

It could be a correctly taken hot-backup.  If this is the case, the directory should have a file named backup_label.  But again, this is not conclusive that the backup is complete and was correctly taken.  You need to test the backup.  The hot backup could be entirely self-contained, all the needed WAL being located in pg_xlog.  Or, it could be usable only in conjunction with an external WAL archive.

Finally, it could be that someone ran cp -r or similar against the database when it was in service without going through the proper steps, and created a useless backup.

You should be able to look at the backup script to at least see what it is attempting to do, that should be easier than doing a from-scratch forensic inspection of the back up directory.


Can this SQL cause unclosed quotation mark?

No - it is NOT your SQL!

To answer this, I did the following:

CREATE TABLE test
(
  num INTEGER,
  name VARCHAR (50)
)

INSERT INTO test VALUES (1, 'Paoul');
INSERT INTO test VALUES (2, 'Mary');
INSERT INTO test VALUES (3, 'O''Hara');
INSERT INTO test VALUES (4, NULL);

then,

SELECT * FROM test;

gives:

num     name
___     _____
1       Paoul
2       Mary
3       O'Hara
4       (null)

And then ran:

SELECT ISNULL(CONVERT(NVARCHAR(MAX), name), 'NULL') AS Foo FROM test;

And got the result:

Foo
____
Paoul
Mary
O'Hara
NULL        <----- String literal, not the value NULL!

Which is what you'd expect! Quite why one would want to insert the string literal NULL is beyond me - Hmm... Developers: "three-value logic, my head is melted - I'll just stick in the string and we can do an OR!". Or, was this system in Oracle at one point? Empty string and NULL string being the same thing?

So, whatever your problem is, it is not your SQL as shown above causing this problem! Check the SQLFiddle here (note: using MS SQL Server 2014!).

As for your point about it being a version bug - I find it difficult to believe that such a basic functionality would be buggy in a 2012 Server version. Such a bug would have broken loads of legacy code!

My hunch is that the problem lies somewhere with the XML part of the code and the bits we can't see. I would set up logging (of all queries) and check out the SQL text that is actually being sent to the server (on my test system).


Be careful with your terminology. Your former slave is now the master.

If you have identical hardware, then there's no need to "switch back".

Regardless, you will need a new slave. You can use the former master for this; either reinitialise it in the same way you created the slave in the first place (probably using pg_basebackup), or use a tool such as pg_rewind or rsync to synchronise them. And then enable streaming replication.

If you want to "switch back", you will need to go through all the above again. That's why I run my slaves on identical hardware so I have no need to "switch back" -- I just setup a new slave (usually reusing the former master).


depending on Version utf8 is a synonym for utf8mb3 or utf8mb4.
so you are always compatible with the version and you can restore a backup without problems, if the version has no utf8mb4 support


Server quit without updating PID file when trying to start MySQL server?

I think the problem might be the PID file
There are two I can see
One in the directory
/usr/local/mysql/data/mysqld.local.pid

and one in the error message
/usr/local/mysql/data/Nicks-MacBook-Pro.local.pid

Please delete those two pid files if they still exist.
Then, start mysql and let’s us know what happens.
I have suggested this twice in the past, 4 years ago and 6 years ago
Before doing so, check to make mysqld is not running.
ps -ef | grep -v grep | grep mysqld

If you see mysqld still running, kill the process, then delete the pid files and retry starting mysql.
UPDATE 2021-01-29 15:56 EST
As it turns out, there were two installations of MySQL involved on the Mac as was learned in the Chat Session.
One installation was done as a dmg install; the other was a homebrew installation.
Once it was discovered that homebrew was competing with the dmg for the PID file, it was decided to stop using the dmg version and use the homebrew version via brew services approach.
The two installs were butting heads, having different PID files.


Can this array group count query be improved?

I doubt that your query can be further optimized for performance.  Right now it is making good use of indexes so there is not much more to be done.  The only example that might be worth trying might be to move the CTE into a view but I doubt that will have much of an effect either way (it may be worth trying however, because CTE scans are somewhat special, and they are separately planned, which makes no change in your favor here).

One thing I would point out is that there are a few things that can be done for readability.  In PostgreSQL, the convention is to use "count(*)" which the planner knows to mean to just count the number of rows returned.  This does not use up more memory than count(1) or count(9) and it is a lot more readable.   With an expression like count(9), I encounter it so seldom, I actually have to stop and think about what it means, which is not a good thing when it comes to queries.


How to recover the innodb when mysql data dir moved from one system to another system?

There are basically two ways to move mysql data from one DB server to another

OPTION 1 : Use mysqldump

mysqldump produces a logical representation of the mysql data. After dumping data as follows

mysqldump -uroot -ppassword --all-databases --routines --triggers --flush-privileges | gzip > /root/MySQLData.sql.gz

Next, scp the /root/MySQLData.sql.gz over to /root of the new DB server

Then, on the other DB, delete everything in /var/lib/mysql except /var/lib/mysql/mysql. In other words, make sure when you do ls -l /var/lib/mysql, you should only see the mysql subfolder in it.

Finally, load the data in the new DB Server

gzip -d < /root/MySQLData.sql.gz | mysql -uroot -ppassword -A

OPTION #2 : Copy /var/lib/mysql folder over to /var/lib/mysql on the new machine

This entails copying the physical representation of the data. You can tar gzip that folder and copy it as you have already done. If mysql cannot startup on the target server, check the COMMON NEEDS first, then run rm -f /var/lib/mysql/ib_logfile* and try restarting mysql on the target DB server again

COMMON NEEDS OF BOTH OPTIONS

Make sure of the following:

Copy the /etc/my.cnf of the source DB server over to the targat DB server
The same version of MySQL is on both servers
There is enough space on the mount point of /var/lib/mysql on the target DB server
Run chown -R mysql:mysql /var/lib/mysql on the target DB server

CAVEAT

If the DB on the source DB server has only .frm files, ib_logfile0, ib_logfile1, and ibdata1, chances are you have innodb_file_per_table disabled. It is very important that you have the exact same innodb setting in /etc/my.cnf on the target DB server as you had on the source.

Hope these help !!!


Should SQL Server statistics be included in versioning?

If I am manually creating statistics then yes I would source control those, like I would any other database object. That way if I roll out a fresh environment from source I know it has everything required to function.

Why would I create manual statistics? Well I’d do this if I need control over the maintenance of them, for example if I am using table partitioning then I will manually create statistics and set them to incremental build and with no recompute. This way I can update the statistics manually as part of my ETL, just after I have switched in a new partition of data.


Is it possible to CREATE EVENT to another server?

Replication DOES NOT require an open connection. There's even an entry in the FAQ for this.

B.13.1: Must the slave be connected to the master all the time?
No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates.

If anything, replication is the most efficient solution when you only want to send changes from a master server to a replica periodically


Add Constraint to check value exists in a non-unique composite key column

As a possible answer to my own question, this could be achieved by adding a Check constraint with a function such as the following:

CREATE FUNCTION dbo.CheckSequenceKey (@SequenceKey nvarchar(10))
RETURNS bit
AS
BEGIN
  DECLARE @retval bit

  IF EXISTS (SELECT 1 FROM dbo.Seqs S WHERE S.SequenceKey = @SequenceKey)
      SET @retval = 1
  ELSE
      SET @retval = 0

  RETURN @retval
END;
GO

ALTER TABLE dbo.Sometbl
  ADD CONSTRAINT chkSequenceKey
  CHECK (dbo.CheckSequenceKey(SequenceKey) = 1);

EDIT:

The Check constraint prevents inserts and updates to the 'Sometbl' table where the new value for 'SequenceKey' is not found within the 'Seqs' table and this is my preference over the use of a trigger.

However, the check constraint doesn't restrict updates to values in the 'SequenceKey' column in the 'Seqs' table or the deletion of entire rows from this table, which could lead to orphaned records in the 'Sometbl' table. Therefore I've taken the below from EzLo's answer to be applied in addition to the check contraint and function for a more complete solution:

Then for dbo.Seqs you can rollback operation that
attempts to update or delete if the SequenceKey is being
referenced:

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnDelete ON dbo.Seqs
    AFTER DELETE
AS
BEGIN

    IF EXISTS (
        SELECT
            'deleted records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t delete SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END

-------------------------------------------------------------------

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnUpdate ON dbo.Seqs
    AFTER UPDATE
AS
BEGIN

    IF UPDATE(SequenceKey) AND EXISTS (
        SELECT
            'updated records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t update SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END