Does postgres optimise repeated expressions? select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1

for this query, is calculation of :input <-> cube(0) repeated for every row?
Also, for
select calculation(row) from table_1 row where condition(row, calculation(row))

is calculation repeated in both the select, and where?

Yes, the expression will be evaluated multiple times. This is easy to test:
test=> \timing
Timing is on.
test=> SELECT pg_sleep(id)
       FROM (VALUES (1), (2), (3)) AS x(id)
       WHERE pg_sleep(id) IS NOT NULL;
 pg_sleep
══════════

(3 rows)

Time: 12013.768 ms (00:12.014)

If you want to avoid that, use a subquery:
test=> SELECT v
       FROM (SELECT pg_sleep(id) AS v
             FROM (VALUES (1), (2), (3)) AS x(id)) AS t
       WHERE v IS NOT NULL;
 v
═══

(3 rows)

Time: 6007.817 ms (00:06.008)


SQL Server 2008 R2 Express size I have what I believe is SQL Server 2008 R2 Express installed on a production server. When I connect to it with SQL Server Management Studio it shows up as SQL Server 10.50.1600.

If it is in fact SQL Server 2008 R2 Express, my understanding is that the database size limit is 10 GB. However, something is just bugging me because I am close to 1 GB in size and there is a message on the properties screen that is scaring me. The database is running in a production environment. There were a lot of rows in it and I deleted a lot, but it has not changed the message. When I go to the properties for the database it says "Size  1005.75 MB" and right below it says "Space Available  0.03 MB".

I tried to use "Shrink" but it just flashes and goes away. Is my production server about to crash because the database is too big? Why would that happen at 1 GB when I thought the server should allow 10 GB? How can I reduce it? Deleting records for inactive clients has done nothing.

The "Database Size" on the properties page is showing the size of the data file.  This will be the size of your data, with some empty space for new data.  You can configure the rate at which this empty space is allocated in the database properties.

It says space available 0.03Mb because you've shrunk the data file down - it will grow by itself to allow for new records - this is normal and intended behaviour.  This "Available Space" is what's left in the current data file, NOT the difference between your current database size and the licensed limit of 10GB.

You can verify the edition that is installed by checking the General page in the Server properties under Product.  In the management studio header it only shows the general version, not the product edition.

When you delete a lot of records, you will end up with free space left in the file.  This will not be automatically shrunk unless you set up a maintenance task for it.


ORA-01017 when connecting remotely as sysdba Good morning,

While installing a database for Oracle Entreprise Manager Cloud Control (OEMCC) I can't login as sysdba, neither localy using sqlplus SYS@MYSID as sysdba nor remotely using the OEMCC installer :

[oracle@myserver ~]$ sqlplus SYS@MYSID as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 10:14:28 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

I did some research :

Here : “ORA-01017: invalid username/password” while connecting to an instance remotely as sysdba
On oracle support : Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

It seems to be a different case :

Localy I can connect using the system user (not as sysdba) : sqlplus system@MYSID then enter password, it works. So tnsnames.ora is correct. (Either localy and remotely)
The password file is correctly created, using : ${ORACLE_HOME}/bin/orapwd file=${ORACLE_HOME}/dbs/orapwmysid password="*********" force=y (just to be sure I connected using sqlplus / as sysdba then did alter user sys identified by "*********" account unlock;)
The parameter remote_login_passwordfile is set to EXCLUSIVE :

    SQL> show parameter remote_login_passwordfile;

    NAME                       TYPE    VALUE
    -------------------------  ------  ---------
    remote_login_passwordfile  string  EXCLUSIVE

The orapw file rights are correct :

    [oracle@myserver ~]$ ls -l $ORACLE_HOME/dbs/orapwmysid
    -rw-r-----. 1 oracle dba 6144 May 29 10:14 /opt/oracle/product/12.2.0/dbhome_1/dbs/orapwmysid

Remote login using system works :

[oracle@anotherserver ~]$ sqlplus system@MYSID

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 13:18:52 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue May 28 2019 15:41:20 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Do you have an idea, something I could investigate ?

Filenames (and Oracle instance names) on Linux are case sensitive. You keep using MYSID but you created the password file as orapwmysid. MYSID as a TNS alias is not case sensitive. MYSID as an instance name is case sensitive and having the password file named lowercase will prevent logging in using SYS with the above error.

[oracle@o71 ~]$ ps -ef | grep smon
oracle     923 31725  0 16:56 pts/0    00:00:00 grep --color=auto smon
oracle   31761     1  0 May15 ?        00:00:37 ora_smon_MIN18
oracle   32529     1  0 16:49 ?        00:00:00 ora_smon_MIN19
[oracle@o71 ~]$ ls -l $ORACLE_HOME/dbs/orapwMIN19
-rw-r-----. 1 oracle oinstall 2048 Apr 30 00:17 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwMIN19
[oracle@o71 ~]$ sqlplus sys/Oracle123@min19 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:56:37 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@o71 ~]$ mv /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwMIN19 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmin19
[oracle@o71 ~]$ sqlplus sys/Oracle123@min19 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:56:45 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

Even if you change the SYS password with ALTER USER..., the password file will not be generated correctly in this case:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:58:19 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter user sys identified by Oracle123;

User altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@o71 ~]$ ls -l /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapw*
-rw-r-----. 1 oracle oinstall 2048 Apr 30 00:17 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmin19
[oracle@o71 ~]$


With SQL Server's Query Store, how can cleanup avoid deleting "least expensive" queries? I'm only interested in resource consumption (QUERY_CAPTURE_MODE = AUTO), with a fairly short interval (e.g. INTERVAL_LENGTH_MINUTES = 5).  I'll be grabbing data from the query store on a fairly regular basis, such as daily, retrieving stats of all completed intervals not previously retrieved.  My concern is that the docs say "Size-based cleanup removes the least expensive and oldest queries first."  I certainly want to discard stats of old queries (older than my retrieval interval), but if a ton of low-resource queries were performed in the intervals I haven't yet retrieved, I don't want those discarded, because they could add up to a high total consumption.

Is there any way to force deletion of stats for old queries, or something else I can do to assure that when cleanup occurs, that it doesn't delete stats for intervals I haven't retrieved yet?

You can also use STALE_QUERY_THRESHOLD_DAYS to clean purely based on time rather than the size-based cleanup parameter.


sp_blitzCache results Memory grants I am running SQL Server 2012 and and using the March 2019 release of sp_blitzCache to analyze some slow running queries on the system.

I have executed the stored procedure with parameter @SortOrder = 'Reads' and found some queries reading tons of pages (in the millions) that need to be reduced.

The odd thing is that none of these queries are returning any information for the memory grant columns, they are all null.

Why is that we can have a query returned from sp_BlitzCache doing many reads and writes, but contain nulls for all memory grant columns?

Thanks,
Kevin

Those columns only become available on SQL Server 2012 if it's patched up to Service Pack 3.

Specifically:

  Update for Dm_exec_query_stats DMV to track memory grants information
  in SQL Server 2012

And KB 3107398.

Just as an FYI, if you're not on Service Pack 4, you're out of Microsoft support.


Custom ID Numbering - fast way on insert? As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern.

I need to assign custom ID numbers for markers during loads. I can not use an identity field, because the ID numbers are to be unique by a "bucket number" - essentially another numbered field.

I currently use the following code:

DECLARE @idRunner smallint
SELECT @idRunner = ISNULL(MAX([Id]),0)
    FROM sim.[Variable]
    WHERE [BucketRef] = @simBucketNo

DECLARE variable_cursor CURSOR FOR
    SELECT DISTINCT p.[Variable]
        FROM simstg.[Parameter] p
        LEFT OUTER JOIN sim.[Variable] v ON (p.[Variable] = v.[Code])
        WHERE p.[BucketRef] = @stgBucketNo
            AND v.BucketRef = @simBucketNo
            AND v.Code IS NULL
OPEN variable_cursor
DECLARE @variable VARCHAR(64)

FETCH NEXT FROM variable_cursor INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @idRunner = @idRunner + 1
    INSERT INTO sim.[Variable] ([BucketRef], [VariableNo], [Code])
        VALUES (@simBucketNo, @idRunner, @variable)

    FETCH NEXT FROM variable_cursor INTO @variable
END

CLOSE variable_cursor
DEALLOCATE variable_cursor

and I Do not like it. It uses cursors which I prefer to avoid.
(Sidenote: this code is untested)

Is there a way to do this more efficient, without a cursor? IR numbers must increase from the highest used when new elements are added. Multiple buckets exist (by bucket no) and have their own numbering.

I am always only going to process data for one bucket at a time (one bucket in sim, one in simstg).

Using a single statement and ROW_NUMBER(). With proper indexes, it will probably be more efficient than cursors.

Note that I moved the p.[BucketRef] = @stgBucketNo from the WHERE to the ON clause. The way you had it, made the LEFT JOIN work as an INNER JOIN.

WITH
  starting (Id) AS
    ( SELECT ISNULL( ( SELECT TOP (1) v.Id
                       FROM sim.[Variable] AS v
                       WHERE v.BucketRef = @simBucketNo
                       ORDER BY v.Id DESC
                     ), 0)
    ),
  variables AS
    ( SELECT
          p.[Variable],
          idRunner = s.Id + ROW_NUMBER() OVER (ORDER BY p.[Variable])
        FROM starting AS s
          CROSS JOIN simstg.[Parameter] AS p
          LEFT OUTER JOIN sim.[Variable] AS v
            ON  p.[Variable] = v.[Code]
            AND p.BucketRef = @stgBucketNo
        WHERE v.BucketRef = @simBucketNo
          AND v.Code IS NULL
        GROUP BY p.[Variable]
    )
INSERT INTO sim.[Variable]
    ([BucketRef], [VariableNo], [Code])
SELECT @simBucketNo, v.idRunner, v.[Variable]
FROM variables AS v ;

The common table subqueries (WITH) are there only for clarity - and to resemble the original code. You can easily rewrite the statement without them:

INSERT INTO sim.[Variable]
    ([BucketRef], [VariableNo], [Code])
SELECT
    @simBucketNo,
    idRunner = ISNULL( ( SELECT TOP (1) v.Id
                         FROM sim.[Variable] AS v
                         WHERE v.BucketRef = @simBucketNo
                         ORDER BY v.Id DESC
                       ), 0)
             + ROW_NUMBER() OVER (ORDER BY p.[Variable])
    p.[Variable],
FROM simstg.[Parameter] AS p
    LEFT OUTER JOIN sim.[Variable] AS v
        ON  p.[Variable] = v.[Code]
        AND p.BucketRef = @stgBucketNo
WHERE v.BucketRef = @simBucketNo
  AND v.Code IS NULL
GROUP BY p.[Variable] ;


Are WHERE clauses applied in the order they are written? I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.

select 1
from workdays day
where day.date_day >= '2014-10-01'
    and day.date_day <= '2015-09-30'
    and day.offer_id in (
        select offer.offer_day
        from offer
        inner join province on offer.id_province = province.id_province
        inner join center cr on cr.id_cr = province.id_cr
        where upper(offer.code_status) <> 'A'
            and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
            and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
    )

Are the WHERE clauses for the date range executed before the subquery?
Is it a good way to put the most restrictive clauses first to avoid big loops for other clauses, in order to do a faster execution?

Now queries takes so much time to execute.

To elaborate on @alci's answer:

PostgreSQL doesn't care what order you write things in

PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone.
The order in which joins are written is also ignored up to the configured join_collapse_limit; if there are more joins than that, it'll execute them in the order they're written.
Subqueries can be executed before or after the query that contains them, depending on what's fastest, so long as the subquery is executed before the outer query actually needs the information. Often in reality the subquery gets executed kind-of in the middle, or interleaved with the outer query.
There's no guarantee PostgreSQL will actually execute parts of the query at all. They can be completely optimized away. This is important if you call functions with side-effects.

PostgreSQL will transform your query

PostgreSQL will heavily transform queries while retaining the exact same effects, in order to make them run faster while not changing the results.

Terms outside a subquery can get pushed down into the subquery so they execute as part of the subquery not where you wrote them in the outer query
Terms in the subquery can be pulled up to the outer query so their execution is done as part of the outer query, not where you wrote them in the subquery
The subquery can, and often is, flattened into a join on the outer table. The same is true of things like EXISTS and NOT EXISTS queries.
Views get flattened into the query that uses the view
SQL functions often get inlined into the calling query
... and there are numerous other transformations made to queries, such as constant expression pre-evaluation, de-correlation of some subqueries, and all sorts of other planner/optimizer tricks.

In general PostgreSQL can massively transform and rewrite your query, to the point where each of these queries:

select my_table.*
from my_table
left join other_table on (my_table.id = other_table.my_table_id)
where other_table.id is null;

select *
from my_table
where not exists (
  select 1
  from other_table
  where other_table.my_table_id = my_table.id
);

select *
from my_table
where my_table.id not in (
  select my_table_id
  from other_table
  where my_table_id is not null
);

will usually all produce exactly the same query plan. (Assuming I didn't make any dumb mistakes in the above anyway).

It's not uncommon to try to optimize a query only to find that the query planner has already figured out the tricks you're trying and applied them automatically, so the hand-optimized version is no better than the original.

Limitations

The planner/optimizer is far from omnicient, and is limited by the requirement to be absolutely certain it can't change the effects of the query, the available data to make decisions with, the rules that've been implemented, and the CPU time it can afford to spend pondering the optimizations. For example:

The planner relies on statistics kept by ANALYZE (usually via autovacuum). If these are outdated, the plan choice can be bad.
The statistics are only a sample, so they can be misleading due to sampling effects, especially if too small a sample is taken. Bad plan choices can result.
The statistics don't keep track of some kinds of data about the table, like correlations between columns. This can lead the planner to make bad decisions when it assumes things are independent when they aren't.
The planner relies on cost parameters like random_page_cost to tell it the relative speed of various operations on the particular system it's installed on. These are only guides. If they're badly wrong they can lead to poor plan choices.
Any subquery with a LIMIT or OFFSET cannot be flattened or be subject to pullup / pushdown. This doesn't mean it'll execute before all parts of the outer query, though, or even that it'll execute at all.
CTE terms (the clauses in a WITH query) are always executed in their entirety, if they're executed at all. They can't be flattened, and terms can't be pushed up or pulled down across the CTE term barrier. CTE terms are always executed before the final query. This is non-SQL-standard behaviour, but it's documented as how PostgreSQL does things.
PostgreSQL has a limited ability to optimize across queries on foreign tables, security_barrier views, and certain other special kinds of relation
PostgreSQL won't inline a function written in anything except plain SQL, nor do pullup/pushdown between it and the outer query.
The planner/optimizer is really dumb about selecting expression indexes, and about trivial data type differences between index and expression.

Tons more, too.

Your query

In the case of your query:

select 1
from workdays day
where day.date_day >= '2014-10-01'
    and day.date_day <= '2015-09-30'
    and day.offer_id in (
        select offer.offer_day
        from offer
        inner join province on offer.id_province = province.id_province
        inner join center cr on cr.id_cr = province.id_cr
        where upper(offer.code_status) <> 'A'
            and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
            and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
    )

nothing stops it from being flattened into a simpler query with an extra set of joins, and it very likely will be.

It'll probably turn out something like (untested, obviously):

select 1
from workdays day
inner join offer on day.offer_id = offer.offer_day
inner join province on offer.id_province = province.id_province
inner join center cr on cr.id_cr = province.id_cr
where upper(offer.code_status) <> 'A'
   and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
   and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
   and day.date_day >= '2014-10-01'
   and day.date_day <= '2015-09-30';

PostgreSQL will then optimise the join order and join methods based on its selectivity and row count estimates and the available indexes. If these reasonably reflect reality then it'll do the joins and run the where clause entries in whatever order is best - often mixing them together, so it does a bit of this, then a bit of that, then comes back to the first part, etc.

How to see what the optimizer did

You can't see the SQL that PostgreSQL optimizes your query into, because it converts the SQL to an internal query tree representation then modifies that. You can dump the query plan and compare it to other queries.

There's no way to "deparse" that query plan or the internal plan tree back to SQL.

http://explain.depesz.com/ has a decent query plan helper. If you're totally new to query plans etc (in which case I'm amazed you made it this far through this post) then PgAdmin has a graphical query plan viewer that provides much less information but is simpler.

Related reading:

Optimizer
Query planning parameters
EXPLAIN
CTEs (WITH queries)
My blog on CTEs as optimisation fences

Pushdown/pullup and flattening capabilities continue to improve in each release. PostgreSQL is usually right about pull-up/push-down/flattening decisions, but not always, so occasionally you have to (ab)use a CTE or the OFFSET 0 hack. If you find such a case, report a query planner bug.

If you're really, really keen you can also use the debug_print_plans option to see the raw query plan, but I promise you don't want to read that. Really.


parse rs.status() output to display one secondary node for mongodump destination I wish to run mongodump to secondary (not primary). So I need as output hostname and port of one secondary.

# ./mongo  -udbadm admin --port 27100 -p --quiet --eval "printjson(rs.status().members.map(function(m) { return {'name':m.name, 'stateStr':m.stateStr} }))"
Enter password:
[
        {
                "name" : "example-1.domain:27200",
                "stateStr" : "SECONDARY"
        },
        {
                "name" : "example-2.domain:27200",
                "stateStr" : "PRIMARY"
        },
        {
                "name" : "example-3.domain:27200",
                "stateStr" : "SECONDARY"
        }
]

With shell script tools is no problem for me to extract one secondary with hostname and port.
My question howto do this job the MongoDB way? What tools do you recommend me?
I heard that jq is better for JSON data. But don't know howto extract only one secondary host.

It's worth noting that if you pass in a replicaset string into mongodump it defaults to reading from a secondary from the set (note: in 3.0.5+ this is not the case if you connect to a mongos in a sharded cluster).  If your replica set name was repl1 it would be something like:

./mongodump --host repl1/example-1.domain:27200,example-2.domain:27200

For reference, in the more recent versions (since the tools were re-written in Go), this is done by setting Monotonic mode when not connected to a mongos (source from 3.0.6 here).

To answer your specific question, a really simple way to parse rs.status() is with a filter, and it will fit on one line:

rs.status().members.filter(function(rsStatus) { return rsStatus.state === 2;})[0].name

That will return the name field of the first (hence [0]) secondary (state === 2) found in the array of members returned by rs.status()


Looking for SQL Server 2000 DTS Components I am trying to upgrade a very old SQL Server 200 to SQL Server 2008 R2. We have many DTS Packages that I need to migrate over and view using SSMS 2008. I know I need the sql server 2000 dts designer components but every Microsoft Link I try fails.

Does anyone happen to have this that they can link to for a download of it?

Thanks in advance!

Fair warning, this is tricky.  I get it working about 9/10 times these days.

FYI First install the 2005 backward compatibility components, then the DTS components.

I believe both are located here: https://www.microsoft.com/en-us/download/details.aspx?id=24793 but no guarantees.


Debugging Postgres error: "could not send data to client: Broken pipe" I'm running a Django 1.8 site, served by nginx and Gunicorn, with a Postgres 9.4 backend.

When I make lots of data-intensive calls to the site's API in succession, I've noticed about 1 in 100 queries failing, and serving up nginx 404 pages.

My first assumption was that the Postgres database was creaking under the load, but the Postgres logs in fact show me this:

2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: LOG:  could not send data to client: Broken pipe
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: STATEMENT:  SELECT ... (psql statement)
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: FATAL:  connection to client lost
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: STATEMENT:  SELECT ... (psql statement)

I think what this is saying is that Postgres is actually running the queries OK, but that it can't send data through to the client.

So I think to debug this problem, I should actually look at Django/Gunicorn/nginx instead.

Have I understood this correctly?

Yes, it seems like you've understood this correctly. The client sent the data to a postgresql backend, and then abruptly disconnected (or was killed). The message is basically "I have an answer for you, but I have nowhere to send it!"

I would definitely look at the logs of the other services and compare timestamps to try and look deeper into what is going on.

Hope that helps. =)


My queries are slow under load I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast.

The same SQL are becoming bottleneck when they are part of a load where response time of this queries varies from 1 second to 100 seconds. There is no high usage of the CPU or memory in the database machine and there is no network latency as well.

I am wondering what else is causing this queries to perform slow under load conditions. During the load itself, sometimes I get the response time below a second, the same query gets response time of 100 seconds in another executions.

Are there any locks or waits happening during a synchronous access of the tables? Most of these queries are SELECT queries only which should not be the cause for locks or waits.

The application environment is Oracle 11g, JBoss 5.1. The elapsed times of the queries are calculated at JDBC level.

I/O looks quiet better, though there are few high physical read ratios. If the I/O is a bottleneck then it should happen for all the time, right? I get slow response times once in a while. If there are locks/waits happening, is there a way to find them out during the test? may be with the help of a perf stats or tool?

I know it's late but there is a lot that could be going on.

-Make sure your statistics look good:

select owner,table_name,sample_size,num_rows,last_analyzed
from dba_tab_statistics.

num_rows and last_analyzed should match if you use default settings.

select owner,table_name,index_name,sample_size,num_rows,last_analyzed
from dba_ind_statistics.

Again sample_size and num_rows should match.

Verify if you use CURSOR_SHARING=EXACT or FORCE (if SIMILAR, change it to FORCE),(will require sysdba privilege):

SQL>show parameter cursor;
SQL>alter system set cursor_sharing=FORCE;
Note that ideally you want to match the parameters you have on your production system. You may want to change these to test them as a cause of your problem however.
Verify that your SGA is sized properly:

select * from v$sga_target_advice
You would get something like this:

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

 10240             .25    131401926              1.2592          1.4876E+10
 15360            .375    117178544              1.1229          1.1815E+10
 20480              .5    112472201              1.0778          1.0648E+10
 25600            .625    109873799              1.0529          1.0250E+10
 30720             .75    107077125              1.0261          9638685159
 35840            .875    105929237              1.0151          9404059270
 40960               1    104353499                   1          9058914623
 46080           1.125    103706507               .9938          8926654470
 51200            1.25    102788197                .985          8724640673
 56320           1.375    102391653               .9812          8646734008
 61440             1.5    101807274               .9756          8515379746
 66560           1.625    101275077               .9705          8394896181
 71680            1.75     97987941                .939          7696453864
 76800           1.875     97967070               .9388          7696453864
 81920               2     97967070               .9388          7696453864

Where SGA_SIZE_FACTOR is 1, is my current SGA_SIZE.
You can look at column ESTD_DB_TIME_FACTOR and ESTD_PHYSICAL_READS to see how much benefit I get if I increase it to the sizes mentioned in SGA_SIZE. In my case, not much benefit which means I'm good.

Compare your query when executed with literal values vs bind variables.
You may want to see if you have a lot of hard parses for a given query. Check v$sql and v$sql_plan (have a look at column child_number for a given sql_id.
Do you have very skewed data on some of the columns in your where clause, joins and group by? If so you may be experiencing issues related to skewed data if bind variables are used.


Running pt-table-checksum on a table in different databases I'm using filters for replication:

replicate-wild-do-table = test_slave.%
replicate-rewrite-db = test->test_slave

Is there an option to specify different DB name on slave for "pt-table-checksum"

pt-table-checksum --host=localhost --databases=test --max-load=Threads_connected:25 --no-check-replication-filters

EDIT 1:

mysql> SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test_slave';
+----------+
| COUNT(*) |
+----------+
|      145 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM percona.checksums;
+----------+
| COUNT(*) |
+----------+
|       40 |
+----------+
1 row in set (0.00 sec)

EDIT 2:

# ls -l *.frm | wc -l
145
# ls -l | awk '{print $3,$4}' | uniq

mysql mysql

It's also complaining that two tables have no indexes and are oversized. It looks like it's just skipping some tables?

I don't see anything along those lines in the Percona Toolkit Documentation.

There is something you can do to cheat

Scenario

datadir is /var/lib/mysql
Master has test database
Slave has test_slave database

You could create a symbolic link in one of two ways

TECHNIQUE #1

On the Master, run

ln -s /var/lib/mysql/test /var/lib/mysql/test_slave
chown mysql:mysql /var/lib/mysql/test_slave

TECHNIQUE #2

On the Slave (if there is no test database), run

ln -s /var/lib/mysql/test_slave /var/lib/mysql/test
chown mysql:mysql /var/lib/mysql/test

NEXT STEP

Once you created the symbolic link, you can run the checksum against test

pt-table-checksum --host=localhost --databases=test --max-load=Threads_connected:25 --no-check-replication-filters

or against test_slave

pt-table-checksum --host=localhost --databases=test_slave --max-load=Threads_connected:25 --no-check-replication-filters

EPILOGUE

When done, you can remove the symbolic link.

Give it a Try !!!


MySQL (Percona): Multiple simultaneous long-running connections I'm running a distributed app in the cloud where clients keep open multiple long-running connections to a MySQL (Percona) server.

After updating to the latest Percona release, the maximum number of simultaneously connected clients has dropped significantly. In the past, with the previous Percona version, it was used successfully with up to 8K connections, now it struggles to get above 3K.

By "struggles" I mean that when the connection bottleneck has been reached, connecting to MySQL times out even in the command line. When I do manage to connect, show processlist doesn't show any hanging queries or locks. So the connections that are established work just fine. The Apache webserver serving the PHP webapp used to monitor the project also hangs indefinitely.

Here's the my.cnf I use, patched together from various guides.

[mysqld]
open_files_limit               = 16384
table_open_cache               = 16384
character_set_server           = utf8mb4
max_connections                = 16384
expire_logs_days               = 10
max_binlog_size                = 100M
innodb_open_files              = 16384
innodb_file_per_table          = 1
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4
innodb_thread_concurrency      = 0
innodb_log_file_size           = 128M
innodb_open_files              = 4000
innodb_flush_method            = O_DIRECT
innodb_buffer_pool_instances   = 1
thread_pool_size               = 16
local_infile                   = 1
skip-name-resolve
thread_cache_size              = 16384
thread_handling                = pool-of-threads
innodb_buffer_pool_size        = 512M
innodb_buffer_pool_instances   = 1
innodb_log_buffer_size         = 64M

And the Percona version I'm running:

mysqld --version
Ver 8.0.15-6 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '6', Revision '63abd08')

OS: Ubuntu 18.04.2 LTS

Hardware:

CPU: Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
RAM: 32G

The queries I'm running are all based on unique indexes (no joins), tables are all InnoDB. The same configuration worked fine until the update to Percona 8.

The my.cnf settings I had to remove after the upgrade as they were no longer supported are:

innodb_locks_unsafe_for_binlog = 0
query_cache_size               = 0
query_cache_type               = 0

Things I tried:

I ran mysqltuner.pl but didn't get any relevant advice from it.
ulimit -n and ulimit -s show values significantly higher than what I'm using (1048576 and 16384 specifically).

Any other suggestions?

There have been only 92 Connections in the 5 hours of Uptime, and no more than 4 at once (Max_used_connections).  So I don't understand your question about struggling with 3K connections.

Observations:

Version: 8.0.15-6
32 GB of RAM
Uptime = 04:54:20; some GLOBAL STATUS values may not be meaningful yet.
Are you sure this was a SHOW GLOBAL STATUS ?
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Extremely high disk activity on the buffer_pool.  Since you have 32GB, and assuming it is mostly for MySQL, change innodb_buffer_pool_size to 22G.

Why so many CHECK, SET, DELETE, SHOW COLUMNS statements?

You seem to have some really big queries.  Turn on the slowlog to catch the worst of them.  For example, maybe a DELETE is missing a useful index?

The table cache is not very effective, increase table_open_cache to 2000.

Max_connections = 15574 is quite high.  Especially since the most used is only 4.  Drop it to 100.

Details and other observations:

( Innodb_buffer_pool_reads ) = 28,825,842 / 17660 = 1632 /sec -- InnoDB buffer_pool I/O read rate
-- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 61,160,824 / 17660 = 3463 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 512M / 32768M = 1.6% -- % of RAM used for InnoDB buffer_pool

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 979 / (8530 + 979) = 10.3% -- Effectiveness of table_open_cache.
-- Increase table_open_cache and check table_open_cache_instances.

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 28,825,842 / 997216830 = 2.9% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size if you have enough RAM.

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 50,177,127 / 997216830 = 5.0% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size if you have enough RAM.

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((28825842 + 61160824) ) / 17660 = 5095 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3% -- Buffer is in RAM; file is on disk.
-- The buffer_size should be smaller and/or the file_size should be larger.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 248,832 / (17660 / 3600) / 2 / 48M = 0.0005 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,660 / 60 * 48M / 248832 = 59,535 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 201 / 0 = INF -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 50177127 + 224 ) / 17660 / 200 = 1420.6% -- If > 100%, need more io_capacity.
-- Increase innodb_io_capacity if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( expand_fast_index_creation ) = expand_fast_index_creation = OFF -- ALTER and OPTIMIZE may be greatly sped up by using ON.
-- Probably better to be ON.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 15,574 -- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections is too high and various memory settings are high, you could run out of RAM.

( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 436) / 1804 = 24.2% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Select_scan / Com_select ) = 217 / 199 = 109.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries.

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log ) = 15,574 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections) may help when doing lots of connections.

( thread_cache_size ) = 16,384 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- Greater than 100 may lead to OOM.

( thread_cache_size / Max_used_connections ) = 16,384 / 4 = 409600.0%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Bytes_received = 6.2 /sec
Bytes_sent = 145 /sec
Com_insert = 0
Com_select = 41 /HR
Handler_read_key = 0.45 /sec
Handler_write = 0.33 /sec
Innodb_background_log_sync = 0
Innodb_data_writes = 77 /HR
Innodb_data_written = 222 /sec
Innodb_pages0_read = 0
Innodb_rows_inserted = 0
Innodb_secondary_index_triggered_cluster_reads = 0.32 /sec
Max_used_connections = 4
Open_files = 2
Select_range = 0
Table_locks_immediate = 2.7 /HR
Table_open_cache_hits = 0.48 /sec
innodb_default_encryption_key_id = 0

Abnormally large:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 2,841
Com_check = 22 /HR
Com_create_db = 0.2 /HR
Handler_read_next / Handler_read_key = 51,277
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 33,884
Innodb_buffer_pool_pages_made_not_young = 12596 /sec
Innodb_buffer_pool_read_ahead = 1209 /sec
Innodb_data_pending_reads = 0.82 /HR
Innodb_data_read = 41774567 /sec
Innodb_data_reads = 2841 /sec
Innodb_pages_read = 2841 /sec
Threadpool_idle_threads = 9
Threadpool_threads = 11
gtid_executed_compression_period = 0.057 /sec
host_cache_size = 1,381
innodb_max_dirty_pages_pct_lwm = 10
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
slave_pending_jobs_size_max = 128MB

Abnormal strings:

bind_address = 0.0.0.0
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
have_ssl = YES
have_symlink = DISABLED
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
optimizer_trace = enabled=off,one_line=off
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
thread_handling = pool-of-threads
transaction_write_set_extraction = XXHASH64


Connect to Azure KeyVault via SQL Connect failed with Error code 3113 I have a task to enable SQL Server database encryption with an Azure Key Vault. I follow the steps described in Set up SQL Server TDE Extensible Key Management by using Azure Key Vault.
More details as following

In my Azure portal, I created a Key Vault, named DB-Encryption-POC and added a Key management Access policy with all permissions.
In the Key vault I created a RSA 2048 Key named DBEncryptionKey1
In Active Directory I registered an App named test-keyvault
In Key Vault DB-Encryption-POC, I added a Key management Access policy with all permissions and set the service principal as test-keyvault. I hope this will hook my app test-keyvault with the access permissions
In the App test-keyvault, I created a secret

You said in 2. you created an rsa 2048 key named DBEncryptionKey1, but the error shows you are trying to find a key named EncryptionDBKey1, looks like at the very least the name is mismatched?


Trigger to compare inserted value with value from another table I have two tables:

Stockitem (serialno, qty)    serialno is the primary key
and
Order(serialno,orderno,qty) orderno and serialno are the primary keys.

I want to create a trigger in order to ensure that when I input the quanitity in the Order table, the quantity should be less than that of the Stockitem. How can I accomplish this? I am using Oracle SQL.

According to your question the sample tables are follows:

  Table:ITEMS

Field's Name      Type
------------      ------
Serialno          Number (PK)
Qty               Number

Table:ORDERS

Field's Name      Type
------------      ------
Orderno           Number (PK)
Serialno          Number (PK)(FK)
Qty               Number

The following trigger may solve your problem.

CREATE OR REPLACE TRIGGER check_qty
AFTER INSERT OR UPDATE ON ORDERS
FOR EACH ROW
DECLARE
 item_qty ITEMS.qty%type;
BEGIN
 SELECT qty
 INTO item_qty
 FROM ITEMS
 WHERE serialno = :NEW.serialno;

IF(:NEW.qty>item_qty)THEN
 RAISE_APPLICATION_ERROR(-20250,'QTY is greater than stock qty!');
END IF;
END;


MySQL localhost vs Amazon RDS instance I am seeing unexpected behavior with some MySQL performance.

When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on Amazon RDS (medium MySQL 5.5.x) takes almost 0.094s.

What could be causing this behavior?

Are you running this test between RDS and MySQL workbench on your own machine?

The MySQL client (and MySQL workbench) are including the time it takes for RDS to return the value of "1" to your workstation (from my workstation on the other side of the world, the response from RDS US West takes 280ms).

If you try enabling profiling on the RDS service, you can see how long the query is really taking - it's completing on your RDS instance in fractions of a millisecond.

mysql> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> select 1;

1
1

1 row in set (0.27 sec)

mysql> show profiles;

Query_ID   Duration      Query
       1   0.00016200    select 1

mysql> show profile for query 1;

Status                  Duration
starting                0.000052
checking permissions    0.000008
Opening tables          0.000012
...
executing               0.000013
end                     0.000008
...
cleaning up             0.000003

12 rows in set (0.28 sec)

set profiling = 0;

Query OK, 0 rows affected (0.28 sec)


SQL insert data simultaneous I have a small label printing software that print sequential numbers. It selects the last serialnumber on sql db's and add one.

The problem I have is that I need now two users to print sequential serial numbers.

SQL Select statement:

sql_query.CommandText = "SELECT TOP 1 cast (serialnumber as nvarchar) as serialnumber,
                                indexnumber, versao
                         FROM  etiquetas
                         where partnumber Like '" & partnumber & "'
                         ORDER BY serialnumber DESC"

Sql Insert Statement:

 "if not exists (select serialnumber
                 from etiquetasklc.dbo.etiquetas
                 where partnumber like '" & partnumber & "'
                 and serialnumber like '" & serialnumber & "')
  begin
      INSERT INTO etiquetasklc.dbo.etiquetas(Nome_Posto, data, Lote, serialnumber, partnumber,
                                             Versao, indexnumber, Semana, QRGerado, operador)
      VALUES( '" & posto & "','" & data & "','" & ordemfabrico & "','" & serialnumber & "','" & partnumber & "',
              '" & partnumber2 & "','" & indexnumber & "','" & weekyear & "','" & codigo & "','" & operador & "')
  end "

The problem occurs when they hit the print button simultaneous.

Can anyone help me

I'm no coder and the SQL is wrote based on my limited skills

I think 2 options that can help you:

Instead of getting the last number and add 1 you can create a table with an IDENTITY column (SerialNumber). When you insert one row the identity column adds the increment you specify (in your case, 1). If 2 users hit the button at the same time SQL server is capable to manage the concurrency and it will assign different numbers.
The same solution as number 1, but instead of using IDENTITY column you extract the next serial number from a sequence object. You create this sequence object and define the "Starts With" number and "Increment" number. Same way about the users, you extract the number from the sequence and this number can only be extracted once avoiding concurrency issues. With this solution, you have to keep in mind that if by any reason your insert fails the serial number is now lost, once you extracted it the counter of the sequence was incremented even though your insert was not committed.

Here you have the official links for extra details about how to create an identity column and a sequence object:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017


Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID? I'm setting up 5.6 Percona Mysql, in order to enable GTID should I have to enable log_slave_update on both Master and Slave ?

If I should enable log_slave_update, then I'm counting this to CONS for having GTID.

Also if you can let me know any other issues in having GTID enabled?

The MySQL Documentation suggests it : See Setting Up Replication Using GTIDs

Keep in mind that MySQL won't even start up with it. Why ? From the Documentation:

  Step 3: Restart both servers with GTIDs enabled.  To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:

  shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &

Since binary logging is active on both Master and Slave, the Master's binary logs and the Slave's binary logs will both have GTIDs recorded along with the transactions. This makes it possible for auto-positioning a Slave to its Master using the latest GTID in the Slave when starting Replication. One of the key steps clearly says in that same document page:

  Instruct the slave to use the master as the replication data source and to use auto-positioning, and then start the slave.

Therefore, --log-slave-updates must be enabled in order for the auto-positioning mechanism to be active. Even just beyond the mechanism, @jynus points out in his answer to mysql 5.6 gtid replication: is log_slave_updates required? that mysqld won't even bother to start if --log-slave-updates is not specified.


Data type for currency 'type' column, e.g. usd, cat, etc. I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details:

No more than 10 different currencies will be used
All currencies have three-letter ISO names
The currency column will sometimes be used in an CASE statement, and sometimes in a WHERE statement
The table will be quite large so I want to make the optimal choice. Storage size is not an issue. Speed and to a lesser extend ease of use is.

I am considering:

Using a char (3) field and saving the currencies as usd, cad,
etc.
Using an enum field and defining the 10 currencies as
options
Using a tinyint field and relating this to another
database that holds the iso code for each currency.
Using a
tinyint field and instead of relating a database (and having to do
JOINs), I just save the list of very static and       non-changing
currencies in a PHP array. Saves me some JOINing, but       still
allows me to use tinyint.

Anybody any suggestions as to what would be best?

3 letter ISO currency codes are relatively small, so there is little overhead in using them as the key for the reference table.  Having certain items such as dates, GL codes and other analysis codes and currency codes directly on the tables is quite convenient for people reporting off the transactions and balances.  Many accounting packages - even big players like Oracle Financials - do it for this reason.


Creating tables with inheritance I'm fairly new to SQL. I just wanted to create a database from scratch to learn.

So I have a table called Addresses and I want to create two tables dependent on it that are Country and State, Im not sure what script to use for that.

Thanks in advance

I am not sure that I would consider this to be an issue of inheritance, and from the description, I would say that the Address table would depend on the other two tables instead of those two tables depending on the Address table.

But that minutia aside, I would recommend:

If you are only storing U.S. or maybe U.S. and Canada addresses, then you can do a "StateRegion" table. But in that case you don't really need a Country table.
If you are storing addresses from many countries, then you would want a Country table, but the StateRegion table is trickier as that varies greatly between countries. I will have to look to see if there is a standardized list for that.

If you want to attempt a "StateRegionProvince" table, check out this info on ISO 3166-2: http://en.wikipedia.org/wiki/ISO_3166-2

For countries, a Country table should be just the ISO list of countries. There is really no need for a separate ID field as the ISO country codes are an acceptable standard (hence the "S" in ISO :-). Just make CountryCode (I prefer the 2-character codes over the 3-character codes) a CHAR(2) NOT NULL.

Full ISO 3166-1 list of 2 character codes (most up to date): https://www.iso.org/obp/ui/#search
ISO list of 2 and 3 character codes: http://www.nationsonline.org/oneworld/country_code_list.htm
ISO 3166-1 and others list: http://www.geonames.org/countries/

If storing non-US/Canada-only addresses:

For "Zip Code", do a VARCHAR(15) field for "Postal Code" as there are various ways of doing those as well.

VARCHAR is fine as the only characters used are: 0-9, A-Z, [space], and [hyphen]
Postal Codes general info: http://en.wikipedia.org/wiki/Postal_code
Postal Code formats by country: http://en.wikipedia.org/wiki/List_of_postal_codes

Use NVARCHAR instead of VARCHAR for the Address, City, StateRegion, etc fields.

So, generally speaking:

CREATE TABLE County
(
   CountryCode CHAR(2) NOT NULL PRIMARY KEY,
   CountryName VARCHAR(100) NOT NULL
);

CREATE TABLE Address
(
   AddressID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   UserID INT NOT NULL, -- FK to some User table
   AddressLine1 NVARCHAR(100) NOT NULL,
   AddressLine2 NVARCHAR(100) NULL,
   City NVARCHAR(100) NULL,
   StateProvince NVARCHAR(100) NOT NULL, -- or INT FK to StateProvince table?
   PostalCode NVARCHAR(20) NOT NULL,
   CountryCode CHAR(2) NOT NULL -- FK to Country table
)


What's the correct behavior for mongoDB when mongo hosts in two DC can't talk to each other Let's assume there are two data centers: DC1 and DC2.

In DC1:

Two mongodb hosts, one primary, one secondary. All mongo hosts in DC1 have the readPreferenceTag: dc:"DC1"
Two app host, the readPreferenceTag has set to dc:"DC1" within the mongo URI.

In DC2:

Two mongodb hosts, both set as secondary with priority 0 (can never be selected as primary). All mongo hosts in DC2 have the readPreferenceTag: dc:"DC2"
Two app host, the readPreferenceTag has set to dc:"DC2" within the mongo URI.

All Mongo hosts in both DCs are in one replica-set.

In normal workflow, app hosts in DC1 will W/R to DC1 mongo hosts and app hosts in DC2 will write to the primary mongo host in DC1 and read from mongo hosts in DC2.

My question is: If there's an network issue between the mongo hosts in DC1 and DC2 (mongo hosts in one DC will not be able to talk to the mongo hosts in the other DC but app hosts can still talk to mongo hosts in both DC), the app hosts in DC1 can still perform R/W operation with mongo hosts in DC1.

But what's the correct behavior for the app hosts in DC2?

From the config status(rs.status()) in primary mongo host we can know that all mongo hosts in DC2 will be in 'unreachable' state, so will app hosts in DC2 still read from DC2 mongo hosts or they will read from DC1 mongo hosts instead?

In this strange situation where somehow mongod processes are network partitioned (between DC's) but application nodes are not. DC2's mongod nodes stays on "SECONDARY" status.

So, YES, DC2 applications still keep reading from DC2 mongod nodes even if data is not updated from primary.

BUT, in this layout your PRIMARY (at DC1) won't stay as primary if there is network partition between DC1 and DC2, because you have four voting nodes and after loosing two votes (DC2) there is no majority.

You need arbiter and it should be located at DC3. There should never be 'even' vote count in replica set.


ElasticSearch: is this problem suitable for ElasticSearch DB? I have some hardware instruments each one recording a signal and generating sequences of timestamp/value pairs. Timestamp is a basic UTC posix time and value is a double.

I'd like to record such pairs in a database for later retrieval. Because we want to scale up across multiple servers we ruled out relational databases and opted out for a NoSQL solution.

The core usecase will be to retrieve a set of values from a given time period (starttime, endtime).

Is ElasticSearch a good solution? What about MongoDB?

For your use case of key-value data, where the value is simple, MongoDB is more than you need.  It is a document-oriented data store for complex value types.  A specific key-value store would suit your needs.  RIAK is the one I've looked at though several others exist.

Since your retrieval will be by time range i.e. key range, Elastic's full-text capabilities will, again, be overhead for which you have no payback.

You don't say what your scale out requirement is, whether local write and global read or full replication of the whole data set etc. Several relational engines offer distributed features.  MariaDB has Galera, for example.  SQL Server has distributed, partitioned views. Other popular systems can afford similar capabilities.  NeoDB is relational and multi-host by design.  The relational model is based on theoretically sound principles and has decades of industry development. You would be doing yourself a disservice by discounting it out of hand.

And your data does have structure, even if it is a simple structure. You have a primary key (device & time) and a functionally dependent attribte.  Whether you define this declaratively in a schema or implicitly in your program's data structures, it still exists.


SQL Server 2016 replication between Windows 10 and Windows Server 2012 I've been asked to set up SQL Server 2016 replication between a development PC running Windows 10 Home and Windows Server 2012 R2. Both run developer versions of SQL Server 2016.

I have found this tutorial: Replication Tutorials SQL Server 2016 and later

This is when I realised that the Windows 10  Home edition does not allow you to create extra users and groups, and in order to do so I would need to spend another £120 to upgrade to Windows 10 Pro.

I've never set up server replication at all, my question is:

Is it possible to setup replication between those operating systems and SQL Servers Dev Editions?

I thought I ask before spending the money just in case it is not possible or I will hit ridiculous amount of problems doing it.

I tried finding anything on that online, to no avail. The Editions and Supported Features for SQL Server 2016 do not mention SQL Server 2016 Developer Edition and the only version from that list that supports Windows 10 is SQL Server 2016 Standard.

https://msdn.microsoft.com/en-GB/library/ms143506.aspx shows SQL 2016 Dev as supported on Win10 Home, and I don't know of any explicit feature limitations--though you are right in general that you're limited to what the OS supports.

Have you tried to set up replication using the built-in service logins? I believe the instructions around adding Windows logins are for security best practice, which may not be an issue for a dev environment.


DB2 select from final table update gives SQL0199 error Goal: I am trying to retrieve records that were affected by UPDATE query.

Based on IBMs article I can use the following query:

SELECT empno, salary FROM FINAL TABLE
(UPDATE employee SET salary = salary * 1.10 WHERE job = 'CLERK')

However when I try to run similar query in database I get the following error:

[42601][-199] [SQL0199] Keyword UPDATE not expected. Valid tokens: INSERT.

Looking at the Db2 for i 7.2 Knowledge Center, shows that only INSERT's are valid in data-change-table-references, not UPDATE's

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafztabref.htm

data-change-table-reference

|----+-FINAL-+--TABLE--(--INSERT statement--)--+--------------------+----|
     '-NEW---'                                 '-correlation-clause-'

  If a data-change-table-reference is specified, the intermediate result table is the set of rows inserted by the INSERT statement.

The link you referenced in your original question is for DB2 for LUW (Linux, Unix and Windows) 9.7. This is essentially a different product than Db2 for i.


Unique Index/Constraint with multiple columns, one column is nullable My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL.

Example: A table named 'regulated_person' has a number of columns including LAST_NAME, FIRST_NAME, BIRTH_DATE and ALIAS. The data type for BIRTH_DATE is date, for the others it's String/VARCHAR. LAST_NAME, FIRST_NAME and BIRTH_DATE are not nullable, i.e. they require values. ALIAS serves as a tie-breaker for cases where two or more people have the same first/last names and are born the same day. Because the tie-breaking situations are not common I want to avoid the need to provide an ALIAS value unless it's necessary.

By way of background the primary key for the table is handled by an auto-increment generator. The purpose for the unique index is to provide a 'business key' that will identify the row without having to resort to the primary key. I'm working with MySQL 5.1.35 and Hibernate ORM ver. 4.3.10.

Any suggestions as to server/database providers apart from MySQL would be welcome.

Thanks in advance for any guidance, and also apologies in advance for any unintended violations of 'forum protocol' as this is the first time I've submitted a question ANYWHERE.

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear but you can test the behaviour:

  A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

What you could do is to define the alias as not null and add a default value, (say 'NONE' or 'DEFAULT' or the empty string ''). You (or the user) will not have to provide that value, it will be automatically saved in all rows. Once someone tries to add a new row with same last name, first name and birth date as an existing row, the unique constraint will forbid it. I guess you could add some procedure at that point, that asks for  a different value for the alias and adds the new row with it.


SQL Server 2008r2 keeps hogging CPU not responding after a while online Please provide some advice to an accidental DBA.

We are currently hosting in an Azure VM an instance of a SQL Server 2008r2 DB. It is being used to hold data relative to a few websites and it has been online and working 24/7 for 15 months except for one hiccup related to the log file size (which we solved switching to SIMPLE recovery mode).

Well, the problem is that recently every few weeks or so, the MSSQL process starts hogging ~50% of CPU usage in the host machine and stops serving some basic queries (it stucks forever processing the query blocking every other pending or new query). There aren't any processes running (spid > 50) and the daily backups seem to work as usual. The Error log file shows no strange events.

Restarting the DB takes almost 8 hours (which I ignore if it's normal) and only delays the problem a few weeks. I'm not sure at all what may cause the problem, as there is no shortage of disk space and, well, I'm quite unexperienced in these scenarios.

Any ideas? I'm fearing is log-related or somehow involves disk caching but I'm completely lost here. I'd honestly appreciate advice for a newcomer on how-to diagnose and isolate the problem.

Additional info: Some of the queries that the DB dislikes involve TOP and ORDER BY clauses which when removed work flawlessly. Particularly this case amuses me:

SELECT TOP 101 * FROM [table] ORDER BY id DESC

...hangs the DB completely and...

SELECT TOP 100 * FROM [table] ORDER BY id DESC

...works flawlessly in 00:00:00.

SQL Server CPU usage stuck at 50% and no process running is not possible, so I suggest that you troubleshoot this condition better. sp_WhoIsActive is a great tool to capture active sessions on your instance.

Regarding the long restart time, I concur that 8 hours is a disproportionate time for recovery. This can only mean that you're undoing something that has been running for quite a while and has not been committed to disk yet. What that "something" is must be determined before cycling the instance. Again, sp_WhoIsActive is your friend.

Regarding the TOP 101 sort, this is a well known problem: Paul White wrote a nice article on the topic back in 2010.

Basically, SQL Server can use an alternative sort algorithm to sort up to 100 rows, but has to switch to full sorting when more than 100 rows are requested, hence the abnormal performance difference.

Solutions range from "dirty tricks" to rewrite the same query in ways that trick the optimizer to what IMHO is the best solution: avoiding the sort altogether by adding an index.

Reasons why this hangs the machine completely are beyond me and would need additional investigation. Things you could look into are:

tempdb filling due to enormous spills
long blocking chains


Restore rman backup on another server with a higher Oracle release I would like to know if it is possible to restore a full backup made with rman of an Oracle9i Enterprise Edition Release 9.2.0.6.0 on another server with Oracle9i Enterprise Edition Release 9.2.0.8.0, I have the doubt precisely in the change of versions of 9.2.0.6.0 to 9.2.0.8.0.
Thank you very much

The data dictionary versions/contents won't match. You would need to restore to the same version and then do an upgrade of the restored database. Either that or create an empty database in 9.2.0.8 and use exp/imp to migrate just your data and other application-specific content, rather than RMAN.


Are view references in a query properly called "derived tables" as such? While answering a question on stackoverflow, I presented a definition of derived tables:

  A derived table is a complete query, inside of parentheses, that is used as if it were a real table.

But a commenter objected:

  Though there are other kinds of derived tables besides those "inside of parentheses". ... [such as] Views and Table-Valued Functions ... .

and further backed this up with:

  From the ISO/IEC 2003 Spec, section 4.3 of the Framework volume, page 13 of the August 2003 draft Spec: "An operation that references zero or more base tables and returns a table is called a query. The result of a query is called a derived table." Note that both Views and Table-Valued Functions return "the result of a query", which is a derived table. Microsoft (and to a lesser extent, Oracle) are notorious for mistakenly equating "derived table" and "sub-query" in their documentation, but Derived Tables do also include pre-defined queries like Views.

So what's the real scoop, here? Am I to relegate what I thought of as derived tables to simply "FROM clause aliased inline subqueries" or are views not properly derived tables?

Please note: I searched for quite a while online and could not find anything definitive. I don't have a copy of the said spec.

Also, I think it's worth addressing something else. Let's say that views are properly called "derived tables". Does this make the reference to the view also a "derived table" or merely a reference? For an example with a CTE that should drive the point home:

WITH SalesTotals AS (
   SELECT
      O.CustomerID,
      SalesTotal = Sum(OrderTotal)
   FROM
      dbo.CustomerOrder O
   GROUP BY
      O.CustomerID
)
SELECT
   C.Name,
   S.SalesTotal
FROM
   dbo.Customer C
   INNER JOIN SalesTotals S
      ON C.CustomerID = S.CustomerID;

The SalesTotals CTE introduced with WITH is a derived table. But is INNER JOIN SalesTotals also a derived table, or just a reference to a derived table? Does this query have two derived tables or one? If one, then by extension I think that a view may be a derived table, but referencing it may not have to be a derived table.

From a SQL Server perspective, a table expression is a named query. This concept of a named query seems in line with what @a_horse_with_no_name commented on earlier. There are four types of of table expressions which are:

Derived Tables
Common Table Expressions
Views
Inline Table-Value Functions

A Derived Table is a subquery that returns an entire table result.
A CTE is like a derived table but usually thought to be more readable and modular. It also has a recursive feature. A View is a reusable named query in which the definition of the table expression is stored. The primary difference between a View and an Inline Table-Valued Function is that the latter takes parameters whereas the former does not.

This has been compiled from the discussion on Table Expressions in Ben-Gan, et al, Querying Microsoft SQL Server 2012, Chapter 4.


Readable join for execution plan in Oracle I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan.
Example:
Consider this query:
SELECT f.year, f.geoid, a.age_band, sum(f.num_people), sum(f.num_insured)
FROM fact f
  JOIN age_bands a ON f.age_code = a.age_code
WHERE f.gender_code = 1
GROUP BY f.year, f.geoid, a.age_band

When I try to see the execution plan with this command:
SELECT s.sql_id, p.*
FROM v$sql s CROSS JOIN LATERAL (SELECT * FROM TABLE (
  dbms_xplan.display_cursor (s.sql_id, s.child_number, 'ALLSTATS LAST')
)) p
WHERE s.sql_text LIKE '%f.gender_code = 1%';

I get different values for the Predicate Information values (for different child numbers)
for instance - for child number 0:
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="A"."AGE_CODE")
   6 - filter("F"."GENDER_CODE"=1)

for child number 1 (and 2):
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("F"."AGE_CODE"="A"."AGE_CODE")
   4 - filter("F"."GENDER_CODE"=1)

My questions are

what is "ITEM_1" and why does it replace "F"."AGE_CODE" ?

is there a better way to understand what joins were made in the DB?

is there a simple way to convert the alias in the Predicate Information to the full table name? (for instance convert "A" to "AGE_BANDS" in the above example)

That is because of query transformations like for example Join Factorization.

Simply put, with JF, queries like this:
select t1.c1, t2.c2
    from t1, t2, t3
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2
  union all
    select t1.c1, t2.c2
    from t1, t2, t4
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Can be automatically rewritten by the optimizer to something like this:
select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2
                   from t2, t3
                   where t2.c2 = t3.c2 and t2.c2 = 2
                 union all
                   select t2.c1 item_1, t2.c2 item_2
                   from t2, t4
                   where t2.c3 = t4.c3) VW_JF_1
    where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

You can see a step called VW_JF_... in your plan whan this happens.
There are other transformations as well that can cause this.

This is too generic and unclear what you want.

Not that I am aware of. You can however display an alias mapping with the format=>'alias' option:

SQL> explain plan for select count(*) from t1 a where a.object_id = 1;
Explained.
Default output:
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - access("A"."OBJECT_ID"=1)

14 rows selected.

Same with alias mapping:
SQL> select * from table(dbms_xplan.display(format=>'alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

   1 - T1
   2 - T1 / A@T1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"=1)

20 rows selected.

SQL>


Optimal data type for storing numbers in SQL Server Similar to varchar that stores strings of varying length, is there an alternative for numbers of varying length that allocates space depending on the size of the number?

I have a large table with 50 columns holding about a billion rows including 15 numeric(10) columns. My quick proof of concept with 4 million showed that I can save a bit of disk-space by reducing the size of these 15 columns, hence this question. Please advise.

POC details - I reduced the column size from numeric(22) to numeric(5) for 15 columns with 3.5 mil rows and disk usage dropped from 1398.02 MB to 981.23 MB.
I will have to push this up to numeric(10) as size 5 will not suffice.

Note: numeric(22) was a bad design I inherited. Changing to integer data type reduced disk usage even further - 907.27 MB. Integer should help if our data model standards team allows this [since it contains no precision]. Will keep posted.

The server version and edition is

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17
  2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition
  (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

You can use row compression Row Compression Implementation that in designed to solve exactly this problem.
And you can also think about page compression that first applies row compression and then tries to achieve space savings using dictionary
Update: available also in Standard started with SQL Server 2016 SP1


How to install Oracle sqlplus, rman utilities on local Windows workstation? I am the administrator for our organization's Oracle database.  I would like to install sqlplus and rman client utilities on my local workstation so I can administer the database without having to remote to it.  I have Googled for instructions on how to do this but cannot find any helpful information.

How can I install sqlplus, rman utilities on my local workstation?

First, download the full Oracle Database Client package that matches the version of your Oracle database.  If your machine is 64-bit, download the 64-bit package. Note: you must download the full client, not the "Instant Client." 2020-04-29 UPDATE: There is now an Instant Client optional package that includes sqlplus (but not rman - it needs the full client).

Unzip the package and run the setup program with administrator privileges.  Choose 'Custom' installation.

A few screens will follow.  Choose the language of installation.  When it asks what account you want services to run under, just choose "Windows Built-in Account," since we won't be installing any services anyway.

It will ask you where you want your "Oracle Base" directory to be.  You can see what I chose in the following screenshot.  The "Oracle Home" location field should automatically set itself according to what you choose for your Oracle base directory.  I recommend not messing with the software location; just stick with the default.

On the next screen we choose what components to install.  Choose "Oracle Database Utilities," "SQL*Plus," and "Oracle Net."

Finish the remaining screens to complete the installation.  SQLplus and rman should now be available to you on your path and command line.

Before you can use sqlplus to connect to your database, you must create and configure a tnsnames.ora file.  That is outside the scope of this question, but once the file is created, it should be placed in its default location, which is <<ORACLE_HOME>>\network\admin folder.  If you cannot determine the location of your <<ORACLE_HOME>> folder, you can find it in your Windows registry at [HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient12Home1] in a string value called ORACLE_HOME.

You should now be able to open a command prompt and type sqlplus username@oraclesid (where oraclesid is the sid of your Oracle database as specified in your tnsnames.ora file) and connect from your workstation.  rman should work from the command line as well.


Understanding “server” pgBouncer docs note:

pool_mode

Specifies when a server connection can be reused by other clients.

session:
  Server is released back to pool after client disconnects. Default.

transaction:
   Server is released back to pool after transaction finishes.

Please explain what “server connection can be reused” and “server is released back to the pool” mean.

I think that in this two lines "server" means: connection to PostgreSQL managed by pgbouncer. "Server connection can be reused" means that established connection to postgreSQL is not closed but kept open and will be used by another client. In this context I understand that "server is released back" to the pool means the same thing: the connection to PostgreSQL is kept open and will be used by another client.

This is just the way connexion pooling works.


Reinitialize all publications I have a database with almost 20 publications, and every once or twice a week a random number of publications needs to be reinitialized.  I have to go and do it manually then every thing works as expected.

I'm using the following command to initialize publication:

exec sp_reinitsubscription @publication = 'PubName', @subscriber = 'all'

The problem is that I have create it for each publication one by one,
so my question is, is there a way to execute it on all publications?

sp_reinitsubscription states that the default for the @publication parameters is "all," so you can execute without that parameter to reinitialize all of the publications.

exec sp_reinitsubscription @subscriber = 'all'


Can plpython3u open files on the file system? I am trying to write a plpython3u function that should open a file in the file system and read some values out of it that get returned by a query. But I am getting a permission denied error when doing so. I am well aware of SQL injection and the dangers of mixing up the database with the file system, this is just for the sake of testing the boundaries for my own knowledge, not for deployment in a production environment.

I tried using chmod 777 on the file in question so that anybody can do anything with it, but I still get permission denied when trying to open the file.

This is the script in question:

CREATE OR REPLACE FUNCTION roof_type_to_name(roof_type TEXT) RETURNS TEXT AS
$$

import xml.etree.ElementTree as ET
ns = {'gml': "http://www.opengis.net/gml",
    'bldg': "http://www.opengis.net/citygml/building/1.0"}
rooftype_schema = ET.parse(r'/path/to/file.xml')
definitions = rooftype_schema.findall(".//gml:Definition", ns)
definition_list = list(definitions)
for definition in definition_list:
    prop_list = list(definition)
    if prop_list[1].text == roof_type:
        return prop_list[2].text

$$ LANGUAGE plpython3u;

I also know that there are other ways of reading xml with postgres, again I am just using this as a personal learning experience with plpython3u with something I am familiar with.

Is it possible at all to open a file on the file system with plpython3u? Or is it totally locked-down for safety reasons?

It is quite possible.  But the OS user running the PostgreSQL server (usually 'postgres') might not have permission to traverse the directory tree containing the file even if it has permissions to the file itself.  Or might be in a chroot jail and so can't even see the directory.  Or it could be blocked by SELinux policy.  This is system's level stuff, nothing specific to PostgreSQL or even databases generally.


Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box.

I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official repository, I need to add some sort of key (apt-key add )[3].

I'm a developer so I don't often setup such systems from scratch and now I'm having to find the right set of documents for apt, postgresql, puppet and ubuntu. However, this stack is popular enough that it should be pretty common.

Any one have steps I can follow to install pgsql 9.3 on a blank ubuntu 13.10 box (using puppet)? I'm willing to blow away my existing vm and re-install 13.10 on my virtualbox to make sure I have a perfectly standard ubuntu server installation. As long as I can re-produce this setup again and again in a declarative manner, I don't mind things like installing from source, etc (although I prefer binary packages).

[1]https://forge.puppetlabs.com/puppetlabs/postgresql

[2]https://forge.puppetlabs.com/puppetlabs/apt

[3]https://wiki.postgresql.org/wiki/Apt

Dunno if it was true when you wrote your question, but the puppetlabs/postgresql module does some pretty clever stuff with versions that are not standard for your OS distro, including installing the required repository.

Snippets from my config:

In one of my puppet classes I call:

    class { 'postgresql::globals':
      encoding => hiera('postgresql::globals::encoding', 'UTF8'),
      locale => hiera('postgresql::globals::locale', 'C'),
      manage_package_repo => hiera('postgresql::globals::manage_package_repo', false),
      version => hiera('postgresql::globals::version', undef),
      notify => Class['postgresql::server', 'postgresql::client'],
    }

and in my hiera config data I have:

postgresql::globals::version: "9.3"
postgresql::globals::manage_package_repo: true

Using hiera like this is optional of course, but it gives you a nice way to use different versions on different hosts.  On later versions of puppet (at least 3.x) you can override postgresql::globals variables without needing that to be part of how you call the package.

You won't need to do stuff from source here, but if you did, I'd recommend that you should build your own package rather than try to automate the build process through puppet.  Avoid it as far as possible though, since it sets you up to do ongoing maintenance of that package.


Query_cache doesn't work with join I have a simple join query, but for some reason query_cache won't cache it!

SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id  WHERE approve =1 AND allow_main =1 ORDER BY FIXED DESC , DATE DESC  LIMIT 7 , 7;

without the JOIN post_plus ON ( post.id = post_plus.news_id ) it's working.

query_cache is on

| query_cache_limit              | 10485760             |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 536870912            |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |

Server version: 5.6.12-56 Percona Server (GPL), Release rc60.4, Revision 393

both tables are innodb with utf8 charset

I'm starting with the assumption that you believe one query gets cached and not the other based on response time the second time you issue the query.  If you're determining that the query isn't cached based on something else, please comment to that effect, explaining your precise observations and what you're expecting instead.

There are several reasons that come to mind why a query would not be cached (or would appear not to be cached).  Here are some thoughts.  Forgive me if I have stated the obvious:

If the 2nd table is particularly busy, anything that gets cached will be cached for such a short time that you'll never see it.  As I mentioned in my response to "Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes" (where I went in to some detail about my finding while investigating the internals of the query cache on stock MySQL) --

  Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results. This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back. The query cache entries referencing that table were already purged.

`query_cache_limit` = 10485760

This setting would prevent a resultset larger than 10MB from being cached.  Adding the 2nd table will obviously increase the size of the resultset but without more information, it isn't apparent how much.

If the 2nd table is partitioned, the query will not be cached because the query cache does not work with partitioned tables (at least in MySQL 5.6; unsure about Percona Server).

The query cache operates on the query string submitted by the client before it reaches the
parser -- for a subsequent query to be served from the cache, it must be byte-by-byte identical to the prior query that was cached.  If you don't submit precisely the same query each time, you won't get a cached result.


Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included I am currently experiencing the error (logged via traceID 1222 ):

keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S AssociatedObjectID = 8205698989
waiterID = process 85b2566 mode=S requestType=wait
waiter-list
keylock hobtid= 820562208 objectname=mydb.dbo.Orders indexname = pk_Orders_OrderID id=lock4012341 mode=X AssociatedObjectID = 820562208
resource-list
Update Orders set OrderType = 1 where OrderID = 52000 and DeliveryID = 1236
inputbuf
Update [Orders] set [OrderType] = @1 where [OrderID] = @2 and [DeliveryID] = @3

Table:

Orders

Columns:

OrderID         int (pk)
OrderNumber     Varchar(50)
OrderDate       datetime
OrderType       int
DeliveryID  int
DateDelivered   datetime
CustomerID  int

Index:

There is a Clustered Index on the PK:

OrderID         int

name pk_Orders_OrderID

The one causing the issue: (I got it by getting the object from AssociatedObjectID  )

_dta_Index_Orders_5_120345_K1_K2_7_12: (nonclustered)

Index Key Columns:

OrderNumber     Varchar(50)
OrderID         int (pk)

Included Columns:

OrderDate       datetime
OrderType       int

Now option "use page locks when accessing the index" caused a lot of pagelocks, so it was unchecked.
The "Use row locks when accessing the index" is still on.

But I am getting the above error as mentioned, and the funny thing is that when customers sometimes run a lengthy report, the keylock occurs
even though the specific report is for Customer A, and the update happening to Orders is for Customers B - thus the CustomerIDs don't match and therefore the should not be no lock, or am I mistaken?

The keylock word in question - is this directly related to the "use row locks when accessing the index"?

Do row locks cause keylocks?

Would it be that the deadlock message:
Transaction (Process ID xy) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
was caused when the _dta_Index_Orders_5_120345_K1_K2_7_12 index was scanning, and at that stage it happend to scan on orderID 52000 (even though OrderID 52000 is not in the resultset)?

I am stunned and have no clue now.
Thank you

Now option "use page locks when accessing the index" caused a lot of
  pagelocks, so it was unchecked

When you play with fire, you get burned. Have you considered what alternative will be used? With page locks disabled, there are two possibilities: use row lock granularity or use rowset granularity. Since there must be at least one row per page, using row granularity will turn into locking at least as many individual locks as page lock, but much more likely more, many many many more locks. So it would turn a bad situation into a far worse situation. Using  rowset granularity will 'heal' the lock manager health as only one lock will be acquired, at the cost of throwing all concurrency down the drain. Do no mess with settings you don't understand. BTW, your question 'do row locks cause key locks' clearly expose the level of your understanding of locking as inadequate to be trusted in changing such important settings, no offense intended.

The important question is why was the query taking page locks? Page locks are the hallmark tellsign of scans: the engine chooses a high granularity because it knows it has a lot of rows to look at. Your post is missing critical information needed to understand the deadlock, please post the entire original deadlock XML, along with the complete schema definition.

As for the question: the 'rowlocks' resource is 'keys' in B-Trees and 'rowids' in heaps.


Send emails based on query results I have a request to send emails the first of every month for each department that hasn't submitted their hours.  I have a query that generates a list of email addresses, the email subject, and the email body.  However, in the Data Flow SSIS Toolbox there is not a "Send Email Task" option like there is in the Control Flow.

How can I send emails based off of my query results?

As @sp_BlitzErik suggests, this is simpler to do in SQL directly. If it needs to be part of an SSIS package for some reason, write a SQL script to do the job, and put that in the package as an "Execute SQL" task. And yes, this is actually one of those rare times where using a cursor actually makes sense.

However, if for some reason you can't use sp_send_dbmail, you can do this in SSIS - just not in the data flow.

Execute the query that generates your list in an "Execute SQL" task, and put the resultset in an object. You may need to modify your query to make sure there's only one resultset - use SET NOCOUNT ON, etc. Then, have a "Foreach" Loop run over the object, putting the various column values in variables. Inside the "Foreach" loop, put the "Send Email" task. Set up all the necessary values as expressions (the last tab in the task editor window), with each expression being the appropriate variable populated by the loop.


MySQL possibility to create global routines (stored procedures and/or functions) Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.

When I tried to create a routine from console (without prior issuing use dbname)
I am getting an error:

ERROR 1046 (3D000): No database selected

We have tons of identical databases (data is different) and the goal is  to
create some triggers for some tablenames. But we want to run only one routine so we don't have to create those routines for every database (since they are identical, routines would work the same for each database).

There is no way to define stored procedures or stored functions (or events) that are global.

One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).

This is something I do with my collection of custom date/time calculation functions (e.g., SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.

If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the DATABASE() function returns the name of the schema under which the routine was defined, not your session's current database.  It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.


Create sequence starting with maximum id from a table I'm trying to create a sequence in the following way:

create sequence some_seq start with (select max(id) + 1 from some_table);

But I'm getting the following error:

ERROR: syntax error at or near "("

I've also tried this approach:

DO $$
DECLARE
    min_seq_value int8 := (select max(id) + 1 from some_table);
BEGIN
    create sequence some_seq start with min_seq_value;
END; $$;

But it gives me:

ERROR: syntax error at or near "min_seq_value"

It seems to be pretty basic but I can't get it working. Any ideas?

Thank you!

Create the sequence, then set the value afterwards:

create sequence some_seq;
select setval('some_seq', (select max(id) from some_table));

You probably also want to "link" the sequence to the table's column as well:

alter sequence some_seq
  owned by some_table.id;

For modern Postgres version (>= 10), it is recommended to use an identity column instead:

alter table some_table
   alter id add generated always as identity;

select setval(pg_get_serial_sequence('some_table', 'id'), (select max(id) from some_table));


What would cause newly restored SQL Databases to show tables, but display an 'Invalid Object' error when trying to view table data? Problem:

As an untrained and involuntary SQL DBA, I'm taking a set of SQL 2008R2 databases and restoring to a new SQL 2008R2 server in another environment.

The restore process goes fine and gives no errors. When I try to connect to them with our database explorer I see the databases and tables populate correctly. Unfortunately when I try to look at a table's data it gives me an "Invalid Object" error.

Background:

When we have performed these moves in the past for other clients, we generally run this script to "repair orphaned users". This is usually all we run and the databases then work with our db explorer and software. If we're going between SQL versions we will also change compatibility mode for the databases with a separate script

use dbar;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbarforms;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbdoc;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhistory;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhl7;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbmds;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbparadox;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbpicklist;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbreport;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbrx;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';

Troubleshooting Steps:

I noticed that our test servers running on MSSQL all show the nurse login as having ownership of the 10 databases. In the user column it says "nurse" and in the default schema column it shows "dbo"

In the case of this server that i'm having issues with, it shows the nurse login as having ownership of the 10 databases, the user column and default schema columns BOTH say "nurse".

So my first guess was that the other environment was setup to use a schema called "nurse" and the new server doesn't have it. I tried to create a new schema called "nurse" in one of the databases and I get this error:

I then tried this on a few tables to see if it worked... no such luck

ALTER SCHEMA dbo TRANSFER nurse.Table1 <--- This actually did fix my issue. I had it backwards before.

My main issue is that I don't even know what the root problem is, just that it doesn't work. I've talked to our developers and others who have some SQL experience and they all were stumped.

Any support would be greatly appreciated. Thanks in advance!

~A-A-Ron

It sounds like the objects are still there but are owned by the schema nurse.  So for example instead of dbo.Table1 it would be nurse.Table1.

Your users aren't see the tables because they are not including the schema part of the name.  If the schema is not included

SELECT * FROM Table1

Then the schema used is the default schema from the database principal (user).  In this case, as you said, it's dbo which is the norm.  An easy way to check this would be to do the following:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'yourtablename'

If the TABLE_SCHEMA value is nurse then that's your problem.  Try changing the default_schema of your principals like this:

alter user [username] with default_schema = [nurse];

Also you could query the tables by including the two part name:

SELECT * FROM nurse.Table1


Adding 2 Case statements I have 2 separate case statements which are producing the correct results:

Case when A_staff_Service_Type.type = 'v'
  then isnull(Indirect_Duration,0)/60.00 else null end

The above produces 6.5 for 1 employee.

Case when pt_service.service_code = 'N06 '
  then isnull(direct_Duration,0)/60.00 else null End

The above produces 5.5 for the same employee.

So I have two separate outputs of 6.5 and 5.5. What I would like is the output to be 12. I have tried

Case when A_staff_Service_Type.type = 'v'
      then isnull(Indirect_Duration,0)/60.00 else null end
+

Case when pt_service.service_code = 'N06 '
      then isnull(direct_Duration,0)/60.00 else null End

But the above results in 0 instead of 12.

This is a query writer within a clinical application based on SQL Server 2008.

Not sure what you mean, but I think you are looking for something like this:

Case
  when A_staff_Service_Type.type = 'v' and pt_service.service_code = 'N06'
     then isnull(Indirect_Duration,0)/60.00  + isnull(direct_Duration,0)/60.00
  else null
end

I also don't know if the AND is correct for your use-case. Maybe you want an OR - but based on the little information you have provided this is impossible to answer.

The obvious answer to "I want the results added" is:

Case when A_staff_Service_Type.type = 'v'
  then isnull(Indirect_Duration,0)/60.00 else 0 end
+
Case when pt_service.service_code = 'N06 '
  then isnull(direct_Duration,0)/60.00 else 0 End


Experience with Scalearc sharding? Does anyone have experience with Scalearc sharding and it's ability to scale ? Does Scalearc needs to have the whole data in memory or does it behave well with disk storage ? I'm thinking to big database application like 10TB and above.

ScaleArc does not do Data Sharding

I actually wrote an earlier post where I evaluated it for my former employer : Experience using ScaleArc in test or production?

It does provide excellent read/write splitting using a single IP address and proprietary caching (ScaleArc's secret sauce).
You configure the ScaleArc server to have one or more Masters and One or More Slaves. It works great with Percona XtraDB Cluster as well as with Master with 1+ Slaves using MySQL Replication.
Each DB Server must have the complete dataset.

I doubt that you have the budget for multiple servers using 10TB of disk each.

What you might be looking for is ScaleBase not ScaleArc.