Create simple database with secure data entry GUIs I need to create a relatively simple database management environment with a secure graphic user interface, including forms, for data entry. In my co What has been used since to fill this need is an Access database file stored on a shared drive, which was made by a partner not me. Several issues are stemming from the fact that the database is locked down for security, so no updates are possible to the interface. I cannot fix any form bugs, and I keep finding them.

What are the best resources to meet my needs?
They are:

data must absolutely be secure
data integrity is fundamental so I need every entry/edit action by data enterers to be securely stored
I know Python, SQL and have used Mongo before
-no user including the admin may have backend access to change the data without a trace (like you can in Access)
I need to be able to produce something very quickly and continue to fix bugs if they occur

Oh boy this is a loaded question and really broad.  You have so many things to consider.  First let's tackle security.  You have to understand your scope or surface area of attack and compliance requirements.

Do you accept credit card data?  You'll need to meet PCI.  Do you have health data?  You'll need HPAA.  Do you have sensitive information?  Then you will need to classify that.  A common formula for security budget and concern if you are not bound to a certain policy is take the percentage of likeliness of attack, how much it would cost, and factor that into how much to spend on security.  You might see hiring a security consultant to review might be worth it in that case.  There's a lot to consider on the scope for the front end as well, for example is this form public to the company, wide area network, the public internet, etc?

One then has to follow all the best practices to keep a secure front end.  Most likely your security holes will come from a un-secure back end but if you do things such as run the front end web apps as for example, the same user as the DB or a user that has elevated DB access you just created a potential for many penetrations into your data simply by not separating users.  Dev/QA/Prod environments being on the same network and sharing resources is another common attack vector.  Encrypt on all layers if possible.  Read up on front end security best practices such as:

Client Side Security Best Practices
MS Best Practices Link

I'd recommend starting with the basic security best practices from CISSP and/or PCI.  This is for security but not compliance.  Are you in fact required to meet compliance as well?  If so, what kind?  If any, you'd better ensure you do and have mitigating controls.

For the back end you'll want to follow best practices as well.  Depending on the DB engine you will have many ways of doing this.  A most common attack is SQL injection where the queries that are being passed are manipulated to be another query and bring back sensitive data.  Thus you will want to create users that have very tightly defined views, stored procedures (if your engine supports it), users, proxy users, and such so that they can access the minimal amount of information required.

Some DB Best Practices

MS Recommendations for DB Security

I'd say keep looking, reading up, patching, testing, run pen testing tools such as This one and come back with a more specific question.  Let us know what DB engine you're using, how you're doing the transport layer, front end, and so fourth.  Good luck!


PostgreSQL - pgAdmin - path to database I am new to PostgreSQL and I can not found info about this. I am using pgAdmin III and my databse files are kept in instalation directory of PostgreSQL. So far so good. But now, I need to create huge DB, that is out of my HDD capacity. So I create RAID and want to move this single DB to this location. How can I specify path for single database to be somewhere else?

I dont want other on the same disk, since I have them on fast SSDs. The big one will be only for some data mining and I will remove it afterwards.

Have a look a tablespaces. You can keep the main and small parts of the database in the SSD drives and migrate the bigger tables to the tablespaces in your other drive.

http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

In alternative the whole database can be put into the tablespace by setting the default tablespace parameter.


Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in? A developer working on my server has fiddled with some settings and knocked my postgresql database offline. I've been messing with it for an hour and am considering just reinstalling postgresql fresh because I know it will work easily then.

I know this is where I database is. /var/lib/postgresql/9.1/main. Can I copy this file, then paste it back in and expect it to work for me? Its for the same machine and everything.

Yes, as long as you install version 9.1 (minor version doesn't matter). And normally you don't need to copy/move them anywhere, the installation will notice there is already a database system set up and will use it. Of course taking a backup is never a bad idea.

Then again, the reason for it being offline should be clear from PostgreSQL logs. Memory allocations can be too high, or some other thing, and this is reported in the log.


How to set simple parameterization for a json_value query (without using plan guides)? I'm trying to run this query, but it fails it fails with The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.

select  mt.pk

from    My_Table mt

where   json_value(mt.json_data, '$.group_id') != ''

As you can see, my parameter 2 is a string literal. The error is due a bug in SQL Server 2016, when forced parameterization is on for the database.

I have confirmed that forced parameterization is turned on in my database, using:

select name, is_parameterization_forced from sys.databases

I am generally aware that you can force simple parameterization by creating a plan guide, then using it for the query. However, reviewing this documentation, I think that may be too complicated for my actual situation. This is partly because in reality, these queries are be generated on the fly and it's not realistic to also generate plan guides for all of them.

Reviewing Query Hint docs, it says

  The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.

Upgrading to SQL Server 2017 is also not currently viable. Turning off forced parameterization for these databases is also not viable.

I am thinking that if I can set simple paramerization on for the query or session, that would be a workaround. Is such a technique possible? Is there any other workaround?

(PS: I realize that looking for solutions with simple parameterization is a little bit X/Y... any help is appreciated)

So far I have collected the following workarounds:

Wrap the json_value function in a dummy subquery. For example,

select  mt.pk

from    My_Table mt

where   (select json_value(mt.json_data, '$.group_id')) != ''

Per David's first comment, try "Optimize for Ad Hoc Workloads" instead of forced parameterization. I haven't tested this, but it's plausible since the problem is forced parameterization.
Per David's second comment, execute the SQL statement dynamically in another database, and reference the original db with 3 part naming. I have confirmed that this works. For example,

exec tempdb.sys.sp_executesql N'select * from TargetDB.dbo.t where json_value(doc, ''$.group_id'') = '''' '


Installing Oracle 11g on Windows SBS 2011 64 bit In the Oracle web site, I found the following requirements:

http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm#CHDHGGFE

Oracle Database for x64 Windows is supported on the following operating systems:
    Windows Server 2003 - all x64 editions
    Windows Server 2003 R2 - all x64 editions
    Windows XP Professional x64 Edition
    Windows Vista x64 - Business, Enterprise, and Ultimate editions

Is it possible to install an Oracle 11g on windows SBS2011 64 bit?
Thanks
Alberto

According to Oracle support document 1307195.1, Oracle's stance is:

  Microsoft Windows Small Business Server

  Windows Small Business Server is a product suite that bundles several
  Microsoft products, including the Windows operating system. Oracle
  Database does not certify with product suites, only operating systems
  and platforms. To distinguish whether Oracle Database certifies with a
  particular Windows Small Business Server version, find that suite's
  operating system version and consult My Oracle Support's certification
  matrix using that operating system version.

Small Business Server 2011 is based on Windows Server 2008 R2. According to the certification matrix at Oracle support, the following versions of Oracle are certified for Windows Server 2008 R2 x64:

10.2.0.5.0
11.2.0.1.0
11.2.0.2.0
11.2.0.3.0
11.2.0.4.0
12.2.0.1.0


SQL Server 2016 Licence Hope you can help me out.

My current SQL setup is as following
Two SQL Instances (2012 and 2016) stacked on a single virtual machine. With the SQL components SSRS and SSIS 2016 installed on same.

The only reason I did this was two lower licencing costs. My 2012 instance is primarily for older apps that aren't certified on SQL Server 2016. With the goal of moving all my databases over to SQL 2016.

My problem, I've had a request from a supplier for a  standalone virtual machine with SSRS 2016 installed for there reports only. I don't believe we are covered to install SQL components such as SSRS and SSIS on a separate virtual machine.

Any thoughts ?
Thanks

May be the following links may help you:

https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing
https://www.brentozar.com/archive/2015/04/microsoft-sql-server-licensing-simplified-into-7-rules/
https://www.brentozar.com/archive/2014/09/first-concern-sql-virtualization-licensing/


How and what is the most efficient way to join two tables, retaining a particular field from both? I have two tables, with the following columns:

Table 1

ID
Component
Data1
Data2

Table 2

ID
Component
Data3
Data4

If I have rows in these tables, with items Component1, Component2, Component3, as entries in the component field for table 1, and Component3, Component4, and Component5 in the component field for table 2, I am looking to query such that I can get all results like:

Component1, data1, data2, data3, data4
Component2, data1, data2, data3, data4
Component3, data1, data2, data3, data4
Component4, data1, data2, data3, data4
Component5, data1, data2, data3, data4

Note that there are multiple entries of Component, and so I will need to group by the field to get aggregate summary data (using count, avg, etc. on the data fields). I am using SQL Server 2012 but need a solution that will work on 2005 ideally, a portable solution that works on other RDBMS would be preferable, but not necessary right now.

My question is two fold:

1) How do I perform this query? Inner, outer, and full joins seem useless, as they only let me retain Component from either one table or the other, not both. I'm unsure if a Cartesian product is the solution, I suspect it may be but I can't quite see how.

2) What is the most performant way of handling this, given that the number of entries in both tables will potentially be massive? Currently I have two SPs that get the summary data for the tables individually. As the SPs return only a handful of rows - maybe 20 to 30 in practise - because they summarise the data, is it more efficient/possible to perform the solution to part 1) above against the SPs rather than as a single query? An alternative I have is to simply merge the results of the SPs in code, I would rather do this on the database but efficiency will be quite important.

Any help appreciated- and I apologise if this question has been asked, it seems like it should be an obvious and common problem, but I couldn't find any help on Google et. al. though it could be that I was just using the wrong search terms!

It's not entirely clear what you are after but I think a FULL outer join would help:

SELECT
    COALESCE(a.Component, b.Component) AS Component
  , COALESCE(a.data1, 0) AS data1
  , COALESCE(a.data2, 0) AS data2
  , COALESCE(b.data3, 0) AS data3
  , COALESCE(b.data4, 0) AS data4
FROM
    table1 AS a
  FULL JOIN
    table2 AS b
      ON b.Component = a.Component ;

If Component is not UNIQUE on one (or both) of the tables, then you could aggreagte and then join:

WITH a AS
  ( SELECT
        Component
      , COUNT(*) AS cnt
      , SUM(data1) AS sum_data1
      , SUM(data2) AS sum_data2
      -- ...
      , AVG(data1) AS avg_data1
      -- ...
    GROUP BY
        Component
    FROM
        table1
  )
  , b AS
  ( SELECT
        Component
      , COUNT(*) AS cnt
      , SUM(data3) AS sum_data3
      -- ...
      , AVG(data3) AS avg_data3
      -- ...
    GROUP BY
        Component
    FROM
        table2
  )
    SELECT
        COALESCE(a.Component, b.Component) AS Component
      , COALESCE(a.sum_data1,0) AS sum_data1
      -- ...
      , COALESCE(b.sum_data3,0) AS sum_data3
      -- ...
    FROM
        a FULL JOIN b
            ON b.Component = a.Component ;


what's result for sql server log backup twice? If I have a sql server database mydb. Then I run a transaction log backup at 12:00pm.
Then run the transaction log backup(either full or differential) again at 12:30pm.

what's result for the second backup? any possible problem for this case?

When you run a log backup, you don't specify full or differential, you just backup the log.

Log backups back up transactions that have taken place since the last log backup, or if a log backup doesn't exist, since the last database backup.

Recovery Point and Recovery Time objectives will direct how often you want to take log backups.

If, for instance, you do a full backup on Monday morning, then do log backups every 5 minutes, you could restore to Monday at 1:45pm by restoring the full backup and every log backup that has taken place up to 1:45pm.


Sql job fails when connection string is updated with port number,where as it succeeds without port number We have a sql job which creates package dynamically based on metadata config values.We have specified connection string of servers in below format.

Source : sat\sql2012,5000
destination:tak3\war2012,55750

our job tries to first validate schema of metadata after acquiring source connection and then creates package and execute it.

We are seeing strange issues with the job,job is executing fine ,if we don't specify port number
Source : sat\sql2012,5000

Even with named pipes it is working,
np:sat\sql2012

But when we specify port number along connection string,job is failing in below order.

1.Package creation started
2.Unable to connect to given source destination Exception from HRESULT: 0xC020801C
3.Package creation failed

Steps we have tried:
1.Make sure port is enabled in firewall ..it is
2.port is set to static
3.Spn is set and there are no duplicates
4.we are able to connect through SSMS locally and remotely
5.checked which node server is listening thru portqry,it is 5000 only

SSIS account and sql agent account are both the same,but it doesnt matter,job working fine without port number,so no issues with permissions

We tried creating test package in server and tested connection,connection succeded too.

No info in error logs or eventvwr,except framework error which get in logs (as pasted above with exe name).
We tried debugging using windbg,when it failed ,it showed same info as eventvwr  logs

Our devs came up with theory,that job requires strong connection while validating schema,so it is failing and it is an infra issue.

We are out of options,even when we ran profiler and procmon,we are not seeing any connection drops from source and destinationCan some one help me on this please on how to debug further.

You should not include the instance name when specifying the port, so instead of

Source : sat\sql2012,5000
destination:tak3\war2012,55750

You should use

Source : sat,5000
destination:tak3,55750

** Note: following queries about this, I can find no documentation to support my answer. Consider it to be my personal experience only.

There is some detailed information on connection string attributes at the following URL,
Using Connection String Keywords With SQL Native Client
which defines the syntax for server addressing as:

[protocol:]Address[,port |\pipe\pipename] for an "Address"

or

Server= [protocol:]Server[,port] for a "Server"


Installation of SQL Server Express LocalDB 2017 I want to install Microsoft SQL Server LocalDB 2017 on my system. I downloaded SQL Server Express Setup (about 5.1 MB). Then I opened it and chose 'Download Media' option and then chose LocalDB. Then it downloaded SqlLocalDB.msi
setup (about 45.9 MB). I ran the setup and installed it on my computer. I don't know what to do next. Can someone help me, please?

LocalDB is a quirky flavor of SQL Server. In most cases, unless there's specific functionality in LocalDB that you need, I'd suggest running the "regular" Express Edition--as you can avoid quirks like the one you're running into. Additional details of differences are included in this article.

LocalDB has an "automatic" instance that is tied to current user. To connect to that instance, you would use a connection string like "Server=(localdb)\MSSQLLocalDB;Integrated Security=true".

You can also create an explicitly named instance using the SQLLocalDB.exe command line utility. Depending on your installed version, the path may vary (in this example, 130 is SQL Server 2016, but you would use 140 for 2017, etc):

C:\> cd "C:\Program Files\Microsoft SQL Server\130\Tools\Binn"
C:\> SqlLocalDB.exe create MyLocalDB
C:\> SqlLocalDB.exe start MyLocalDB

The SQLLocalDB info command can be used at any time to determine the state of an instance, whether it is running or not:

C:\> SqlLocalDB.exe info MyLocalDB

Additional info on creating & connecting to a LocalDB instance can be found on docs.microsoft.com.


Why is this query with WHERE, ORDER BY and LIMIT so slow? Given this table posts_lists:

                      Table "public.posts_lists"
   Column   |          Type          | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
 id         | character varying(20)  |           | not null |
 user_id    | character varying(20)  |           |          |
 tags       | jsonb                  |           |          |
 score      | integer                |           |          |
 created_at | integer                |           |          |
Indexes:
    "tmp_posts_lists_pkey1" PRIMARY KEY, btree (id)
    "tmp_posts_lists_idx_create_at1532588309" btree (created_at)
    "tmp_posts_lists_idx_score_desc1532588309" btree (score_rank(score, id::text) DESC)
    "tmp_posts_lists_idx_tags1532588309" gin (jsonb_array_lower(tags))
    "tmp_posts_lists_idx_user_id1532588309" btree (user_id)

Getting a list by tag is fast:

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui');

Bitmap Heap Scan on posts_lists  (cost=1397.50..33991.24 rows=10000 width=56) (actual time=0.110..0.132 rows=2 loops=1)
  Recheck Cond: (jsonb_array_lower(tags) ? 'qui'::text)
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on tmp_posts_lists_idx_tags1532588309  (cost=0.00..1395.00 rows=10000 width=0) (actual time=0.010..0.010 rows=2 loops=1)
        Index Cond: (jsonb_array_lower(tags) ? 'qui'::text)
Planning time: 0.297 ms
Execution time: 0.157 ms

Getting a list ordered by score, limit 100 - also fast:

EXPLAIN ANALYSE
SELECT *
FROM posts_lists
ORDER BY score_rank(score, id) DESC
LIMIT 100;

Limit  (cost=0.56..12.03 rows=100 width=88) (actual time=0.074..0.559 rows=100 loops=1)
  ->  Index Scan using tmp_posts_lists_idx_score_desc1532588309 on posts_lists  (cost=0.56..1146999.15 rows=10000473 width=88) (actual time=0.072..0.535 rows=100 loops=1)
Planning time: 0.586 ms
Execution time: 0.714 ms

But combining the above two queries is very slow:

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui')
ORDER BY score_rank(score, id) DESC
LIMIT 100;

Limit  (cost=0.56..33724.60 rows=100 width=88) (actual time=2696.965..493476.142 rows=2 loops=1)
  ->  Index Scan using tmp_posts_lists_idx_score_desc1532588309 on posts_lists  (cost=0.56..3372404.39 rows=10000 width=88) (actual time=2696.964..493476.139 rows=2 loops=1)
        Filter: (jsonb_array_lower(tags) ? 'qui'::text)
        Rows Removed by Filter: 9999998
Planning time: 0.426 ms
Execution time: 493476.190 ms

Why? How to improve the efficiency of the query?

Definition of the two functions used above:

create or replace function score_rank(score integer, id text)
  returns text as $$
select case when score < 0
  then '0' || lpad((100000000 + score) :: text, 8, '0') || id
       else '1' || lpad(score :: text, 8, '0') || id
       end
$$
language sql immutable;

create or replace function jsonb_array_lower(arr jsonb)
  returns jsonb as $$
SELECT jsonb_agg(lower(elem))
FROM jsonb_array_elements_text(arr) elem
$$
language sql immutable;

The problem with this statement is, that the query planner has no usable statistics for jsonb_array_lower(tags).

As seen in the first explain:

(cost=0.00..1395.00 rows=10000 width=0) (actual time=0.010..0.010 rows=2 loops=1)

The planner expects 10.000 rows to be returned if he uses the filter jsonb_array_lower(tags) ? lower('Qui') but there are just two rows returned.

The same happens in the last statement. Because of this missing information, the planner assumes, that a scan on the index tmp_posts_lists_idx_score_desc1532588309 would be more efficient.

You could try to avoid the need of lower and normalize the input during INSERT and UPDATE.

Another method would be:

WITH c AS (
    SELECT id FROM posts_lists
    WHERE jsonb_array_lower(tags) ? lower('Qui')
)
SELECT * FROM posts_lists l, c
WHERE l.id = c.id
ORDER BY score_rank(score, id) DESC
LIMIT 100;

This statement uses a CTE as optimization fence, but be careful, the performance may get worse for other WHERE conditions or table content.


How to remove datafile from oracle database which is already remove locally? Created one dbf file from my database using following command,

alter tablesapce PAULA_INDEX add datafile '/u02/oradata/hfd1/index/index17.dbf' size 2000M;

This dbf file is created in my server locally /u02/oradata/hfd1/index/ path. And I am also able to view this file in my oracle database.

Now I want to delete this file from my database. And accidentally it was already removed locally from /u02/oradata/hfd1/index/ path. Now I am not able to drop the file from my database. Please suggest what I can do.
I have tried,

alter tablespace PAULA_INDEX drop datafile '/u02/oradata/hfd1/index/index17.dbf';

But it is not dropped.

You can try:

shutdown immediate
startup mount
alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop;
alter database open


Mongostat shows 2 updates on Primaries when insert data When i insert data to a mongo sharded cluster it shows 2 updates on the Primaries (check localhost:55030). Does anyone knows where these updates come from? I only do inserts using for (var i = 1; i <= 1000000; i++) { db.testData.insert( { x : i , y : (i+10)/2}, {z:2},{w:3} ) }

            insert  query update delete getmore command flushes mapped  vsize    res  faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn  set repl       time
            localhost:55027     *0     *0     *0     *0       0     3|0       0  1.53g  5.58g   871m      0  test:0.0%          0       0|0     0|0   178b     4k     9 rs01  SEC   13:01:28
            localhost:55028     *0     *0     *0     *0       0     5|0       0  1.53g  5.58g   872m      0  test:0.0%          0       0|0     0|0   452b     4k     9 rs01  SEC   13:01:28
             localhost:55029        *0     *0     *0     *0       2     3|0       0  1.53g  5.62g   900m      0  test:0.0%          0       0|0     0|0   272b     4k    24 rs01  PRI   13:01:28
             localhost:55030       427     *0      2     *0     807   801|0       0  1.28g  5.13g   548m     23 admin:0.9%          0       0|0     0|0   276k   239k    25 rs02  PRI   13:01:28
             localhost:55031      *428     *0     *0     *0       0     1|0       0  1.28g  5.06g   501m     25 local:3.8%          0       0|0     0|0    62b     3k     9 rs02  SEC   13:01:28
              localhost:55032     *429     *0     *0     *0       0     1|0       0  1.28g  5.06g   501m     25     .:3.4%          0       0|0     0|0    62b     3k     9 rs02  SEC   13:01:28
               localhost:61027     423      0      0      0       0     424                 2.42g    16m      0                                            57k    24k     3       RTR   13:01:28

I haven't confirmed by looking at code but I'd bet they are updates to the local database regarding your secondaries.  The primary keeps track of the current status of the secondaries in the set (including where they have synced to), so that would be an in-place update to the local.slaves collection.  Similarly, there is local.replset.minvalid which will certainly be changing as you do inserts.

Basically, I wouldn't worry about it, it's not going to be a major factor in your performance (by the way, if you are using 2.6+ then you should read this Q&A on performance of that type of loop in the shell).


Slow Query logging everything I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example)
   Query_time: 0.000237  Lock_time: 0.000064 Rows_sent: 0  Rows_examined: 109

On another server I get these:
   Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 0

Either way they both seem to be logging other queries...or am I reading something wrong?

set-variable = log-slow-queries="/tmp/mysql-slow-query.log"
   set-variable = long_query_time=1

If you're using mysql 5.1.21 or newer, you can change the behavior of logging slow queries with the min_examined_row_length

Set it to something like 5000 or so, and keep your log_queries_not_using_indexes turned on.


You should be able to use a simple INSERT/SELECT FROM.

INSERT INTO TABLE_A (
    a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
    )
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B

If your goal is to combine the results from the two tables into another table, you should do as McNets suggests in his comments and use either UNION or UNION ALL.

SELECT a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
FROM TABLE_A
UNION   --OR UNION ALL IF YOU WANT TO KEEP DUPLICATES
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B


What is the meaning of Order preserving Conflict Serializable schedules? I was going through the concept of order preservation in Conflict Serializable class and I came across Order preserving conflict serializable (OCSR in short). The following is the definition of OCSR that I found:

  History h is order preserving conflict serializable if h is conflict equivalent to a serial history hs where t,t'∈h:if t occurs completely before t' in h then the same holds in hs.

Following is one example of a schedule that is in OCSR:

  w3(y) c3 w1(x)r2(x) c2 w1(y) c1

But I could not get why this schedule is in OCSR. Because as per my understanding this is the conflict graph t3 --- > t1 ---- > t2

which is showing a serial schedule where t1 comes before t2. But in the original interleaved schedule t2 completely comes before t1. Then how is the given example said to be in OCSR?
Can anyone please help me understand this better?

In the schedule, T3 occurs completely before T1 and T2, but T2 and T1 are interleaved (so there is no "one occurs completely before the other" relationship between them).
So, it can be in OCSR as long as there exists a serial history that T3 occurs completely before T1 and T2.
Actually there is a serial history T3->T1->T2 that meets the above condition.
Then, it is in OCSR.


How do I select the next row in SELECT FOR UPDATE? I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process.

How do I do this?

select * from visited where machine='1' order by revisit asc limit 1 for update

Lets say this query pulls website id # 12345

If I run this query on two crawler children, the second one waits for the first one to finish before selecting a row - is there a way for the second query to pull the next row? 12346 for example?

Each instance will select, update revisit to a future time, do work, and then commit.

I think you are using the wrong model of concurrency to solve your problem- SELECT... FOR UPDATE (or modifying the transaction isolation level) is used for those cases where you want to manage a row in isolation of other transactions (concurrency), creating a queue, not to coordinate executing different queries/jobs at the same time (parallelism).

The reason for this is that when you run SELECT... FOR UPDATE (and then supposedly UPDATE the row, then finish your transaction), other concurrent queries over the same query do not fail immediately- they get "locked" until the first one finishes and then lock the row again. This would be a pattern needed, for example, for a counter. You could of course add a column "processed", that changes to 1 when it finishes, so the other queries do not run again (I suggested such design on this other answer: https://dba.stackexchange.com/a/98353/30545 )- but you are wasting precious time being locked for finally not doing anything.

They way parallel processing is handled is to generate a unique list of rows in a concurrent way (with a granularity larger than one, to avoid high concurrency; but not too large so there cannot be idle processors), and then process them without worrying about overlaps. If for any reason you need to process rows in perfect order, you will have to read them in a single thread, then send those to different job runners.

If for any reason you want to still use a "try to lock the row, then move on" model, you can use a very low innodb lock timeout and handle the error, or better, GET_LOCK() / RELEASE_LOCK() with the row id and a very low timeout; but I would highly discourage that for the reasons I just said.

Sorry to be a bit vague, but your questions does not show any code to work with.


Why does "CREATE TABLE IF NOT EXISTS" lock a table? We recently run into "waiting for table metadata lock" issues, after  "CREATE TABLE IF NOT EXISTS" has been fired several hundred times per second.

This is not a request for help (obviously we refactored that code), but I try to understand why that command locks table metadata. It was my understanding that this would be a read-only operation if the table indeed does exist.

Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Thanks

The locks in mysql are un-necessarily restrictive before it even checks if the table already exists.  The bug report, that seems to be being ignored by mysql but has been fixed on a fork of mysql, explains it pretty well:

  If one session had any metadata lock on a table, another session
  attempting CREATE TABLE [IF NOT EXISTS] for the same table would
  hang. This occurred due to an attempt in the second session to
  acquire an exclusive metadata lock on the table before checking
  whether the table already existed. An exclusive metadata lock is not
  compatible with any other metadata locks, so the session hung for the
  lock timeout period if another session had the table locked.

https://bugs.mysql.com/bug.php?id=63144

The fork that fixed it by using the (rather obvious) solution of doing a shared lock to see if it exists, then if it did not exist upgrading to an exclusive lock.


SSIS line number from file I have done some tests in flat file source trying to see if it read the file in order of the number of lines and all of them the number of the line read was inserted in SQL Server equally.

I was wondering if I can be sure that it will always read and insert in this order.

You can use Row Count tasks in your data flow to determine how many rows pass through which part of your solution. This can be helpful if you put it immediately after a source and before a destination... assuming that you are actually performing logic that could eliminate rows.

People will often record these values to SSIS variables and then write this information to a log table of rows read and written per package per execution. Of course, I've seen very few take the time to validate or create tests on all this custom logging work which often times is merely a check on Microsoft's tools. Take the time to consider what new issues you're introducing into your solution, points of failure and dependencies, and the benefits you're creating.

There are good designs that reside in the DB that can easily answer such questions and more...

[AuditInsertDate] DATETIME NOT NULL DEFAULT GETDATE()

Covers additional use cases, provides timing information, and tracks your insert counts (in most load scenarios). It's also a lot easier to implement and maintain than custom SSIS logging.


sqlite3 -Infinity value in Float field I am converting Oracle tables into a sqlite3 Database and stuck at the "-Infinity"-value in a float field.

The snippet of the Oracle "Create Table" statement is

"X1" FLOAT(126)

How do I convert the "-Infinity" value correctly into a sqlite3 table?

I chose "REAL" as data type and just stored "-Infinity" inside it. Seems to work.


There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like:

create table t
( ts timestamp not null
, EngOilP_sd smallint not null
, CompOilLVL_sd smallint not null
) engine = innodb;

insert into t (ts, EngOilP_sd, CompOilLVL_sd)
values
 ('2015-06-24 20:28:07',0 ,0 )
,('2015-06-24 20:30:20',1 ,0 )
,('2015-06-24 20:36:47',1 ,0 )
,('2015-06-24 20:41:11',1 ,0 )
,('2015-06-24 20:43:29',1 ,0 )
,('2015-06-24 20:45:42',0 ,0 )
,('2015-06-24 20:47:51',0 ,0 )
,('2015-06-24 20:49:59',0 ,1 )
,('2015-06-24 20:52:01',0 ,1 )
,('2015-06-24 20:54:17',0 ,0 );

Since we are going to reference the "pivot" several times we create a view for it:

create view t_pivot as
    select 'EngOilP_sd' as sd, ts, EngOilP_sd as val from t
    union all
    select 'CompOilLVL_sd', ts, CompOilLVL_sd from t;

The start time (using @Pauls comment as the definition) is the earliest time where each attribute is 1:

select sd, min(case val when 1 then ts end) as start
from t_pivot
group by sd

The stop time is the earliest time after that where each attribute is 0:

select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
from (
    select sd, min(case val when 1 then ts end) as start
    from t_pivot
    group by sd
) as x
join t_pivot as y
    on x.sd = y.sd
   and y.ts > x.start
group by x.sd, x.start

Adding another level of nesting makes it convenient to determine the duration:

select sd, timediff(stop, start) as duration, start, stop
from (
    select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
    from (
        select sd, min(case val when 1 then ts end) as start
        from t_pivot
        group by sd
    ) as x
    join t_pivot as y
        on x.sd = y.sd
       and y.ts > x.start
    group by x.sd, x.start
) as z
order by sd desc;

+---------------+----------+---------------------+---------------------+
| sd            | duration | start               | stop                |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd    | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| CompOilLVL_sd | 00:04:18 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
+---------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)


Bind MongoDB to IPv4, as well as IPv6 Mongo v. 3.4

respective part of config file:

#network interfaces

net:
  port: 27017
  bindIp: 127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1 (IPs are made up)

No matter the format I've used:

[2001:db8:0:0:0:0:2:1]
[2001:db8:0:0:0:0:2:1%eth1]

I always get:

2017-05-17T09:54:54.358 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1", port: 27017 }, processManagement: { fork: true, pidFilePath: "/var/run/mongod.pid" }, storage: { dbPath: "/var/lib/mongo", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongo/mongod.log" } }
2017-05-17T09:54:54.384 I NETWORK  [initandlisten] getaddrinfo("2001:db8:0:0:0:0:2:1") failed: Address family for hostname not supported
2017-05-17T09:54:54.385 E NETWORK  [initandlisten] listen(): socket is invalid.
2017-05-17T09:54:54.385 E NETWORK  [initandlisten] Failed to set up sockets during startup.
2017-05-17T09:54:54.385 E STORAGE  [initandlisten] Failed to set up listener: InternalError: Failed to set up sockets
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2017-05-17T09:54:54.385 I CONTROL  [initandlisten] now exiting
2017-05-17T09:54:54.385 I CONTROL  [initandlisten] shutting down with code:48

How do I properly bind Mongo to listen on IPv6 address? Note that this is version 3.4, where net.ipv6 is not necessary anymore.

Did you find an answer yet? If not, here is the solution:

IPv6 is disabled by default, you have to enable it for binding on IPv6. The following net-config should work:

net:
    ipv6: true
    port: 27017
    bindIp: 127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1 (IPs are made up)


Optimize view (and underlying table) for averaging timestamps into hours I have this table:

CREATE TABLE spp.rtprices (
  "interval" timestamp without time zone NOT NULL,
  rtlmp numeric(12,6),
  rtmcc numeric(12,6),
  rtmcl numeric(12,6),
  node_id integer NOT NULL,
  CONSTRAINT rtprices_pkey PRIMARY KEY ("interval", node_id),
  CONSTRAINT rtprices_node_id_fkey FOREIGN KEY (node_id)
      REFERENCES spp.nodes (node_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)

And one more relevant index:

CREATE INDEX rtprices_node_id_interval_idx ON spp.rtprices (node_id, "interval");

Against it I've made this view:

  CREATE OR REPLACE VIEW spp.rtprices_hourly AS
  SELECT (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone AS pricedate,
  date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1 AS hour,
  rtprices.node_id,
  round(avg(rtprices.rtlmp), 2) AS rtlmp,
  round(avg(rtprices.rtmcc), 2) AS rtmcc,
  round(avg(rtprices.rtmcl), 2) AS rtmcl
  FROM spp.rtprices
  GROUP BY date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1,
           rtprices.node_id,
           (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone;

The point of which is to give averages of the numeric columns for each hour (The timestamps have data every 5 minutes). The problem is that a query for a single day for a single node_id takes upwards of 30 seconds for 24 records.

explain analyze select * from spp.rtprices_hourly
where node_id=20 and pricedate='2015-02-02'

Returns this:

   "HashAggregate  (cost=1128767.71..1128773.79 rows=135 width=28) (actual time=31155.023..31155.065 rows=24 loops=1)"
"  Group Key: ((date_part('hour'::text, date_trunc('hour'::text, (rtprices."interval" - '00:05:00'::interval))))::integer + 1), rtprices.node_id, (((rtprices."interval" - '00:05:00'::interval))::date)::timestamp without time zone"
"  ->  Bitmap Heap Scan on rtprices  (cost=10629.42..1128732.91 rows=2320 width=28) (actual time=25071.410..31153.715 rows=288 loops=1)"
"        Recheck Cond: (node_id = 20)"
"        Rows Removed by Index Recheck: 7142233"
"        Filter: (((("interval" - '00:05:00'::interval))::date)::timestamp without time zone = '2015-02-02 00:00:00'::timestamp without time zone)"
"        Rows Removed by Filter: 124909"
"        Heap Blocks: exact=43076 lossy=82085"
"        ->  Bitmap Index Scan on rtprices_node_id_interval_idx  (cost=0.00..10628.84 rows=464036 width=0) (actual time=68.999..68.999 rows=125197 loops=1)"
"              Index Cond: (node_id = 20)"
"Planning time: 5.243 ms"
"Execution time: 31155.392 ms"

Simpler view

For this objective:

  The point of which is to give averages of the numeric columns for each hour

.. it seems just as good to truncate to full hours, which is simpler and cheaper:

CREATE OR REPLACE VIEW spp.rtprices_hourly AS
SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
GROUP  BY 1, 2;

Faster query

Either way, an equivalent query on the view with sargable predicates would be:

SELECT *
FROM   spp.rtprices_hourly
WHERE  node_id = 20
AND    hour >= '2015-02-02 0:0'::timestamp
AND    hour <  '2015-02-03 0:0'::timestamp;

This is faster, but still not as fast as it could be. The major performance hit is because the index can only be used with an index condition on node_id, which is preserved in its original state in the view. That's why your index rtprices_node_id_interval_idx with node_id first is important. Why?

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?

The second predicate on hour has to be filtered after the tuple has been fetched from the heap (the row has been read from the table). The large majority of rows is discarded late into the process, much work for nothing.

Much faster with direct query

It would be much faster to run the original query and apply predicates before you aggregate:

SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
WHERE  node_id = 20
AND    "interval" >= '2015-02-02 0:0'::timestamp
AND    "interval" <  '2015-02-03 0:0'::timestamp
GROUP  BY 1, 2;

You'll see index conditions for all predicates now. The more efficient index is still the one with node_id first. Why?

Multicolumn index and performance

Fast & short: create a function

So, this won't work nicely with a view. Use a function instead:

CREATE OR REPLACE FUNCTION rtprices_hourly(_node_id int
                                         , _from timestamp
                                         , _to timestamp = NULL)
  RETURNS TABLE (
    hour    timestamp
  , node_id int
  , rtlmp   numeric
  , rtmcc   numeric
  , rtmcl   numeric) AS
$func$
SELECT date_trunc('hour', r."interval")  -- AS hour
     , r.node_id
     , round(avg(r.rtlmp), 2)  -- AS rtlmp
     , round(avg(r.rtmcc), 2)  -- AS rtmcc
     , round(avg(r.rtmcl), 2)  -- AS rtmcl
FROM   spp.rtprices r
WHERE  r.node_id     = _node_id
AND    r."interval" >= _from
AND    r."interval" <  COALESCE(_to, _from + interval '1 day')
GROUP  BY 1, 2
$func$  LANGUAGE sql STABLE;

Be wary of naming conflicts between OUT parameters and column names. That's why I table-qualified all columns here.

Now you get top performance with a simple query:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp, '2015-2-3 0:0'::timestamp);

I put in a convenience feature, the second parameter defaults to "one day later" if you omit it:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp);

More about function parameters and default values:

Add function parameter to SQL query WHERE clause

You can query any range:

SELECT * FROM rtprices_hourly(1, '2015-2-2 10:0'::timestamp, '2015-2-2 20:0'::timestamp);


How do I add a partition to an existing table in mariadb / mysql? I have the following table.

I want to add partitions too.

CREATE TABLE `app_log_Test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `version` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `app_log_dateCreated` (`dateCreated`),
  KEY `app_log_label` (`label`),
  KEY `app_log_event` (`event`),
  KEY `app_log_level` (`level`)
) ENGINE=TokuDB `COMPRESSION`=tokudb_quicklz

I am using MariaDB 10.

MariaDB [test2]> alter table app_log_Test partition by RANGE(TO_DAYS(dateCreated))(
-> PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')) ENGINE = TokuDB,
-> PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')) ENGINE = TokuDB);

I get the following error

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

First I need to deal with the error

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

This makes sense and partitioning means putting data into different files based on some condition. If the partition condition is not part of the tables primary key index this would not know how where to put the data.

alter table app_log_Test drop PRIMARY KEY, add primary key (`id`, `dateCreated`);

Next, I can re-run my alter table to add the partitions I care about.

ALTER TABLE app_log_Test
PARTITION BY RANGE (TO_DAYS(dateCreated))
    (PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
    PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')),
    PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')),
    PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE);

If I need to add more partitions after that. I don't have to specify the partition scheme again I can just add the partition and its constraints.

ALTER TABLE app_log_Test
    REORGANIZE PARTITION p_max_future_dates INTO (
        PARTITION p_201811 VALUES LESS THAN (TO_DAYS('2018-11-01 00:00:00')),
        PARTITION p_201812 VALUES LESS THAN (TO_DAYS('2018-12-01 00:00:00')),
        PARTITION p_max_future_dates  VALUES LESS THAN MAXVALUE);

My table now looks like this.

show create table app_log_Test;
Create Table: CREATE TABLE `app_log_Test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `version` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`dateCreated`),
  KEY `app_log_dateCreated` (`dateCreated`),
  KEY `app_log_label` (`label`),
  KEY `app_log_event` (`event`),
  KEY `app_log_level` (`level`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `COMPRESSION`=tokudb_zlib

/*!50100 PARTITION BY RANGE (TO_DAYS(dateCreated))
(PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
PARTITION p_201901 VALUES LESS THAN (737425) ENGINE = TokuDB,
PARTITION p_201810 VALUES LESS THAN (737333) ENGINE = TokuDB,
PARTITION p_201811 VALUES LESS THAN (737364) ENGINE = TokuDB,
PARTITION p_201812 VALUES LESS THAN (737394) ENGINE = TokuDB,
PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */


View permissions on specific Oracle object I want to be able to see what permissions have been granted on a specific object in my Oracle geodatabase. What would my syntax look like in SQL Plus?

Thank you.

You can check object privileges on tables through the view dba_tab_privs.

Here's a query:

SELECT grantee, privilege FROM dba_tab_privs WHERE table_name='XXXXX';

You can also learn more about privileges and stuff from this really cool website:

http://www.dba-oracle.com/forensics/t_forensics_object_privileges.htm


Synonym/Alias equivalent for a Database If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full SP each time we push to Live.

We want a single of line of code such as

select col1, col2 from databaseName..ourTable

Where databaseName is somehow set at a server level.

I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?

You can create a synonym to the object, however you cannot currently create a synonym to a database. This is a popular request on Connect:

Expand synonym to other entities (database, linked server)
Allow CREATE SYNONYM for <database>

So, as you've stated in your question, you'd need to run CREATE SYNONYM for each object affected. Like this, in the dev instance:

CREATE SYNONYM MyObjectSyn
FOR Database1Dev.dbo.MyObject;

And like this in the prod instance:

CREATE SYNONYM MyObjectSyn
FOR Database1.dbo.MyObject;

Then the stored procedure code references MyObjectSyn instead.

Another way to get around this problem, that I don't recommend, is to use a linked server connected to each database.  The linked server can be repointed on dev to Database1Dev, and Database1 on prod.

Or, as Scott Hodgin suggested: use dynamic SQL and a configuration table on each server that would 'map' <database1> to a specific database on that server.  The stored procedure would read the configuration table and REPLACE <database1> with the name retrieved from the configuration table.


Mysql query stress testing program We are writing queries for our website, and when we test the queries in a dev environment, the queries run pretty fast. The problem that we are having is the dev environment has less that 10 active users on it, while our live environment has 4,000+ simultaneous users and then our server just cant handle the queries.

So, is there a tool out there that can take some queries and do some stress testing on them, so we can test the queries before we put them live and kill the server?

As was pointed out in the comments, you could go with mysqlslap.

If you are a little more adventurous, you may want to setup you own by deliberately dummying down the MySQL configurations or just collecting query profiles. Here are some of my posts and some of the others that address doing stress testing and query profiling. I hope the ideas help.

Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL

Accepted Answer
My Answer

Mar 10, 2013 : Stress Test to Saturate Memory for MySQL InnoDB

Accepted Answer
My Answer

Jun 08, 2012 : How to improve MySQL Server Performance..??
Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
Jul 09, 2011 : Testing Query Speed (My Answer was MySQL-specific)

Give it a Try !!!


OVER and PARTITION BY for db size change I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the snapshots being evaluated?

Here is the column:

( LAST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

- /* subtract */

FIRST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ) * 8 / 1048576.0
AS usage_change_mb

It's the daily change, if the snapshots are taken daily (snapshot_time column is where I assume you're getting the snapshot time data).

Partitioning restarts computation each time a unique value for the column (or combination of columns) specified in the PARTITION BY is found. Even though you are sorting (ORDER BY instance, db, snapshot) you aren't aggregating data across the partitions in any way without an aggregation function preceding OVER().


SQL Server 2016 - sp_execute_external_script I'm trying to execute a new stored proc in 2016 named sp_execute_external_script. I first need to enable external scripts as follows:

sp_configure 'external scripts enabled', 1;

After doing this, I see this message:

Configuration option 'external scripts enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.

I then execute RECONFIGURE and see a message saying, "Command(s) completed successfully".

I then attempt to execute the following, to see if R is working correctly:

exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go

When I do so, I see the following error:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

Any ideas why?

After turning on external scripts, you'll need to restart SQL Server so that when you run

sp_configure 'external scripts enabled';

it returns a run_value of 1:

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
external scripts enabled            0           1           1            1

Documentation: External Scripts Enabled server configuration option


Optimize query with indexes The following query is looking for all towns that are between 5 and 10 kilometers from a hard-coded latitude/logitude (=another town actually). I have 37010 towns.

I'm using Symfony 2 which "creates" a query through Doctrine. This query is flagged by my MariaDB server as "not using indexes". I dont know what's going on, because:
(1) Here's the query:

SELECT
    v0_.id AS id0,
    v0_.nom AS nom1,
    v0_.url AS url2,
    v0_.cp AS cp3,
    v0_.insee AS insee4,
    ROUND(
        6371 *
        ACOS(COS(RADIANS(50.58907000)) *
        COS(RADIANS(v0_.lat)) *
        COS(RADIANS(v0_.lng) -
        RADIANS(3.16710500)) +
        SIN(RADIANS(50.58907000)) *
        SIN(RADIANS(v0_.lat))), 2
    ) AS sclr5
    FROM ville v0_
    HAVING sclr5 > 4 AND sclr5 <= 10
    ORDER BY sclr5 ASC LIMIT 20 OFFSET 0;

Here's the time:

# User@Host: x[x] @ localhost []
# Thread_id: 1514  Schema: mydatabase  QC_hit: No
# Query_time: 0.071503  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 37030

And here's the table:

MariaDB [mydatabase]> desc ville;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| id_origine      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| date_v_creation | datetime            | YES  | MUL | NULL    |                |
| date_v_debut    | datetime            | YES  | MUL | NULL    |                |
| date_v_fin      | datetime            | YES  | MUL | NULL    |                |
| article         | varchar(4)          | YES  |     |         |                |
| nom             | varchar(150)        | NO   | MUL |         |                |
| url             | varchar(150)        | NO   | MUL |         |                |
| cp              | varchar(10)         | NO   | MUL | NULL    |                |
| insee           | varchar(10)         | NO   |     |         |                |
| id_region       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| id_departement  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| lat             | decimal(15,8)       | NO   | MUL | NULL    |                |
| lng             | decimal(15,8)       | NO   |     | NULL    |                |
| sound           | varchar(252)        | YES  | MUL | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

MariaDB [mydatabase]>

If I do an explain on this query i get:

+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|    1 | SIMPLE      | v0_   | ALL  | NULL          | NULL | NULL    | NULL | 36510 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+

1 row in set (0.00 sec)

I have 37010 towns and MariaBN tells me Rows_examined: 37030. I dont get that. Anyway, how would you optimize it?

The problem is that you are only filtering by that large calculation so there is no choice but to do a full table scan and calculate that formula for every row.

You could limit the number of rows considered by adding an index on the lat and long columns and filter by an approximate square as well as the exact crow-flies calculation. That way the query runner should be able to seek using that index to find those in the larger area of the square ("x between n1 and n2 & y between n3 and n4" should use that index (an index seek followed by a partial scan)) then only need to lookup & read the full rows and do the calc & sort on those few matches to find the final filtered/limited set.

If you have the chance to consider changing database backend at this point in your project some (postgres with the PostGIS extensions for example) support special types and indexes for handling geometry data (even sometimes natively supporting lat/long based calculations), though that change may be overkill if this is the only part of your application that would make use of it.


pg_restore importing single table to a file I have a backup in directory format taken with pg_dump.
When I try to export single table from this dump with the command:
pg_restore -Fd mydirectory -t sometable > table.sql

I get the error :
pg_restore: error: one of -d/--dbname and -f/--file must be specified.

When I read the document if no -d parameter passed pg_restore should print the output to stdout.
What I am  missing?

You need to read the documentation for the version you are using.
Since v12, -f - is mandatory to get output to go to stdout.  Having that behavior be obtained just by omitting -d was considered to be confusing.  But since you want the output to go to a file, just name that file:
pg_restore -Fd mydirectory -t sometable -f table.sql


Database Design: Normalization and NULL values I have normalized my table from 0NF to 2NF (hopefully correct).

How do I now go from 2NF to 3NF?

Also, ResponseToTransactionId is a recursive foreign key that will contain NULL values a lot of the time. Is this bad design?

If ResponseToTransactionId is always NULL for items marked 'REQUEST', and contains the 'REQUEST' items ID where the status is 'ALLOW' then It means that from an application point of view you should always be able to locate which records are linked by this field.

And if NULL then you know there is no link.

It is a bit unusual to do it this way, but it should be fine


Maintenance plan fails on non-existent database We have a maintenance plan on our production server which does all the usual stuff... checking integrity, rebuilding indexes, backups, etc.

Lately, it has been failing every night, complaining about a database which does not exist. The maintenance plan is set to work only on All user databases.

However, the plan craps out every night, and gives the following message in the job history:

  Executing the query "USE [RollbackSnapshotTempDB{73B1E0C2-00FC-476A-A67..." failed with the following error:
  "Database 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}' does not exist. Make sure that the name is entered correctly.".
  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

(I changed the GUID, I wasn't sure if that is any sensitive info.)

We definitely do not have a user database (or system database either, for that matter) by that name. And there is nothing listed under the Database Snapshots folder either.

As per Aaron's comment, I queried sys.databases to see if that weird name is listed, but it is not listed in there either. Nor is it listed in SELECT DISTINCT database_name FROM msdb.dbo.backupset.

Running on SQL Server 2012, build 11.0.5343.

In order to identify where this is coming from (because who knows which step in your maintenance plan is actually tripping on this), I recommend running the following tedious code, which will identify views that show the database name even if it is a database that no longer exists in sys.databases (dropped, renamed, what have you):

DECLARE @db SYSNAME = N'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

DECLARE @sql NVARCHAR(MAX) = N'SELECT N''--''';

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM msdb.'
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM msdb.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM msdb.sys.all_objects AS o
 INNER JOIN msdb.sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN msdb.sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM master.'
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM master.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM [master].sys.all_objects AS o
 INNER JOIN [master].sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN [master].sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql;

EXEC sys.sp_executesql @sql, N'@db SYSNAME', @db;

This should produce output in the bottom pane like this (hopefully only a few rows):

--
SELECT * FROM msdb.[dbo].[backupset]
  WHERE [database_name] = 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

You can copy and paste that output into the top pane and run it, and then act accordingly. For rows you find in backupset, for example, you can just delete them. If they are in catalog views this may be a little tougher - tell us what you find and we should be able to figure out how to fix it.

(Also, this isn't exhaustive, there are likely other places a database name might be stored using other variations on the name column. By design, I'm avoiding the obvious places like sys.databases.name, because you claim you've already checked there.)

Now, I would recommend Ola Hallengren's free maintenance scripts or even paid solutions like SQL Sentry Fragmentation Manager* for various types of maintenance, over the cumbersome and buggy native maintenance plans.

Short of that, stop using "all user databases" since this seems to cover more than just the set of databases that are currently present. I'll be interested to hear if you find this database name in any of the catalog views, backup-related, or job-related tables. I also have a tip publishing soon about searching all string columns across all databases on an instance, and it might be even more productive than the targeted script I wrote above. I'll try to remember to come back here and update this post with a link once it has been released.

Disclaimer: I work for SQL Sentry.


mysql freeze/crash on JOIN here is query:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM news as nw
INNER JOIN sources as src ON nw.source=src.source_id
INNER JOIN categories as cat ON nw.category=cat.category_id
WHERE nw.status=1
ORDER BY nw.news_id DESC LIMIT 0,140;

news table size is around 30MB. server RAM is 1GB.

my.cnf:

[mysqld]
max_connections = 120
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8K
query_cache_size = 32K
query_cache_type = 1
tmp_table_size = 16M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
local-infile=0

it was myisam I converted to innodb for better performance but nothing changed so I get back to myisam.

query works correctly on localhost but when ran in server, get high load and eat all RAM until crash mysql. just one of above query get me highload! something like that mysql could not finish it. table has around 50000 records.

I tried to fix issue by changing my.cnf parammeters with no luck.

QUERY

With regard to the query, please make this index

ALTER TABLE news ADD INDEX status_news_id_index (status,news_id);

and refactor the query as follows:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM
(
    SELECT * FROM news
    WHERE status=1
    ORDER BY news_id DESC LIMIT 0,140
) as nw
LEFT JOIN sources as src ON nw.source=src.source_id
LEFT JOIN categories as cat ON nw.category=cat.category_id;

STORAGE ENGINE

Here is the problem with your InnoDB : You did not configure anything for it.

Each storage engine caches differently. MyISAM caches index pages from the .MYI files into the key buffer. InnoDB caches data and index pages in the InnoDB Buffer Pool which you did not configure. The default is 8MB.

There is a default set of innodb files located in /var/lib/mysql

ibdata1
ib_logfile0
ib_logfile1

These must be resized.

Since you are using MySQL 5.1, please do the following:

STEP 01) Convert all InnoDB tables back to MyISAM

STEP 02) service mysql stop

STEP 03) Add the following lines to my.cnf after tmp_table_size = 16M

innodb_file_per_table
innodb_buffer_pool_size=512M
innodb_log_file_size=128M
innodb_fast_shutdown=0
innodb_thread_concurrency=0

STEP 04) Erase InnoDB Files

rm -f /var/lib/mysql/ib*

STEP 05) service mysql start

On startup, mysqld will do the following

create /var/lib/mysql/ibdata1 as a 10MB file, run some stuff, and end up 18MB
create /var/lib/mysql/ib_logfile0 128M
create /var/lib/mysql/ib_logfile1 128M

STEP 06) Convert all MyISAM tables back to InnoDB


SSIS Script Component - How to modify the Output0Buffer I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.

For all the records, I would like to add the Status field which has either "success" or "fail" and this gets output from script component.

I then log those output into a text file.

Problem:
I'm not been able to add status for each input records since the web service call only happens on post execute.

I tried this but still doesn't work:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime

         });
 }

 public override void CreateNewOutputRows()
 {
     MessageBox.Show("Test CreateNewOutputRows");
     MessageBox.Show(listOfData.Count.ToString());
     foreach (var item in listOfData)
     {
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
     }
 }

Everything in a Transformation is done within Input0_ProcessInputRow
The solution would essentially be

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime
         });
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
}

You may be able to perform the output steps in PostExecute, CreateNewOutputRows is not run in Transformation, only in Destination scripts.


With multiple files in a file group, when does data start being placed on the 2nd file in the filegroup? In a previous question, it was suggested that I should add an extra "File" to my existing (Primary) Filegroup to easily add more disk space for my DB.

So I did that and nothing broke.

But I'm not sure when Sql Server will decide to stop putting data into the original File and start putting data over to the new File?

From @davidbrowne's excellent answer to my question, he mentioned:

  SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files.

Okay, great - but when does it 'know' to start filling the other file?

Currently, this is what I have setup ... and please note the MAX FILE SIZE.

Do I need to give a MAX file size (e.g. LIMIT to 1T) for the original File which will then help Sql Server to 'know' when to start using the next (new) File?

There's currently 19GB left on disk F:\.

You'll want to preallocate the space on XWing_ExtraData.   At 10MB per autogrow, it'll induce a lot of external file fragmentation and delays whenever SQL needs to expand the file on the disk (performance will be better if you have a large pre-sized file up front).
Then disable autogrowth on XWing so that it can't grow any further otherwise SQL will continue to populate the file until you run out of space, triggering an 1105 error (filegroup full).
With regards to the original file, my preference is to just disable autogrowth via the dialog box (checkbox on the top of the dialog box or via ALTER DATABASE...MODIFY FILE...FILEGROWTH=0. You want to ensure that SQL won't attempt to autogrow the file if the algorithm can choose that file instead of XWing_ExtraData if both need to autogrow to accomodate the next data insertion.


Unable to run FULL OUTER JOIN Here is my query .

 SELECT user_record.password,user_addresses.street
 FROM `user_record`
 FULL OUTER JOIN `user_addresses`
     ON user_record.id=user_addresses.user_id
 ORDER by user_record.id

here is the error which is happening when i run my query in Phpmyadmin .

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTER JOIN `user_addresses` ON user_record.id=user_addresses.user_id ORDER by us' at line 1

Full outer join is not supported. If you look at:

https://dev.mysql.com/doc/refman/5.7/en/join.html

there is only LEFT and RIGHT outer join. You can however mimic FULL OUTER with a UNION between LEFT and RIGHT:

SELECT user_record.id AS user_id,
       user_record.password, user_addresses.street
FROM `user_record`
LEFT JOIN `user_addresses`
    ON user_record.id = user_addresses.user_id

UNION

SELECT user_addresses.user_id,
       user_record.password, user_addresses.street
FROM `user_record`
RIGHT JOIN `user_addresses`
    ON user_record.id = user_addresses.user_id

ORDER BY user_id ;

You have to include user_record.id / user_addresses.user_id in your select clause to be able to order by it.


How to Backup and Restore Filestream Data I am trying to backup and restore my database which includes a filestream filegroup.

I backed up the database without the filestream filegroup and was interested to know what happens when you restore it. Inside the restore dialog nothing appeared in the 'Select backup sets to restore' list. This is not exactly surprising.

Then, I backed up the filestream data into a separate .bak file.

When I tried to restore them both together I got this message,

  The media loaded on 'backup file' is formatted to support 1 media
  families, but 2 media families are expected according to the backup
  device specification.

What does that mean?
How can I backup filestream and database separately?

You have to restore the database first with the norecovery switch, then restore the other file group. Then roll the transaction log forward then bring the database online.

This requires a minimum of three RESTORE DATABASE commands to do.


multiple join HOW to get the following result? I would like to get the following result. I have tried with the following scripts to get TAB1 and TAB2 but I couldn't show up the result like that.

This is my code for TAB1:

select branch_code,ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
         from gltb_gl_bal_daily
        where gl_code in ('620100000',
                          '620230000',
                          '620400000',
                          '620800000',
                          '620910000',
                          '620930000',
                          '621010000',
                          '621020000',
                          '630000000')
          AND trn_dt = &PM_DATE --entry
        group by branch_code  ;

Resultset:

--------TAB1-------------
id      Amount
-------------------------
1       5791920.396
2       4900
3       103.95
4       4420.265
5       46.2
6       23.1
7       500
8       0.03

and this is script for TAB2:

select branch_code,ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
                 from fcubs1203.gltb_gl_bal_daily
                where gl_code in ('620820000', '630300000')
                  AND trn_dt = &PM_DATE
                  group by branch_code;

Resultset:

--------TAB2-------------
1       593023.5

===> this is the desired result that i would like to show:

--------Result-----------
1       5198896.896  (TAB1.id-TAB2.id)
2       4900
3       103.95
4       4420.265
5       46.2
6       23.1
7       500
8       0.03

Simply join those two queries:

with tab1 as (
  select branch_code,
         ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
  from gltb_gl_bal_daily
  where gl_code in ('620100000','620230000','620400000','620800000',
                    '620910000','620930000','621010000','621020000',
                    '630000000')
    AND trn_dt = &PM_DATE --entry
  group by branch_code
), tab2 as (
  select branch_code,
         ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
  from fcubs1203.gltb_gl_bal_daily
  where gl_code in ('620820000', '630300000')
  AND trn_dt = &PM_DATE
  group by branch_code
)
select tab1.branch_code, tab1.a35 - coalesce(tab2.a35, 0)
from tab1
  left join tab2 on tab1.branch_code = tab2.branch_code;


How to send+update on duplicate a big time series to SQL server I use a SQL Server 2008
I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that).
What is the best way to do so?
I have been advised to use

MERGE

and

BULK INSERT

but,

can we use the 2 at the same time?
I don't want to save my data first to a file. It is already in memory, why should I have to save it first to a file? Plus I can have several threads calling this method at the same time and it is going to be a bit messy.

Any advice welcome

If the data is in memory, you can use SQLBulkCOpy in .net or similar to send data to SQL Server. No need to instantiate a file.

And load a staging table first in SQL Server. Then use MERGE from this staging table to the actual table

If you don't want a persistent staging table, create a #temp table and use that in the subsequent MERGE. I'm not sure about the usefulness of Table Valued Parameters here

If you want to load the final table directly, then you can MERGE directly from a file using OPENROWSET(BULK…). But you don't want to instantiate a file.

In summary: there is no direct "in client memory" to final table solution: you need an intermediate table or file


MySQL: Importing databases from older version I'm in the process of moving everything from a very old production server to a new one. I'm now trying to copy the MySQL database over to the new system. I have very limited knowledge of MySQL, BTW. First some version info:

Old server:

$ mysql -V
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

New server:

$ mysql -V
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

On the old server, I made a dump of all databases first:

mysqldump -u root -p --all-databases > all_databases.sql

Then on the new server, I imported the databases with:

mysql -u root -p < all_databases.sql

But now, when I try to run mysql, I get an error:

$ mysql
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format

So apparently it has overwritten the user database as well (I really had no idea that would happen, but, you know, limited MySQL knowledge...).

So how can I fix this?

Writing out @Akina's suggestion:

Edit your MariaDB server options file, which I think is it at /etc/mysql/my.cnf on Debian. In the [mysqld] section, add this line: secure_auth=OFF
Restart MariaDB server: systemctl restart mariadb (assuming you have systemd)
You should now be able to log in using the mysql client and run something like: SET PASSWORD = password('your_new_password'); Alternatively, run mysql_secure_installation from the command-line.
Edit the MariaDB server options file again and remove the line you inserted before, then restart the MariaDB server, and everything should be working, hopefully.


How can I backup/dump MYSQL database with foreign key constraints? I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.

I am taking the dump with following command:

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

What am I missing?

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.
The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.
You can easily change all your database to use InnoDB instead of MyISAM:

add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)

modify existing tables with alter table my_table engine=innodb

to modify all tables you can try this more complex statement:
SELECT concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
FROM information_schema.tables t
WHERE t.table_type = 'base table'
  AND t.table_schema = 'my_db'
  AND t.engine != 'innodb'

Now all your foreign keys should be respected and when you use mysqldump you will se them.


SQL Server native log backup running every 5 minutes What will happen if SQL Server native log backup is running ever 5 minutes and due to high active transaction the backup was not able to complete in 5 minutes ?
Will the next log backup be initiated after 5 minutes or will it be waiting for the log backup to complete.
I searched for resources online but couldn't get a proper content. Any reference will be much appreciated Thanks.

Will the next log backup be initiated after 5 minutes or will it be waiting for the log backup to complete.

If the log backup is run by SQL Server Agent
If the scheduled time comes up and the prior instance of the job is still running, it will postpone until the next scheduled time. Two instances of the same job can not run at the same time.
If the log backup is run by some other scheduler software
You'll have to check with your vendor's documentation or support team, but I can't imagine any software allowing this to behave any differently than SQL Server Agent, unless they have an option to not skip collisions and queue them up, and you have enabled that option.


Can not drop table due to replication error, though table is not being published I am attempting to drop a table in a database that is a publisher. The table in question is not being published. The error I get is:

Msg 15021, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 14 [Batch Start Line 1]
Invalid value given for parameter @procmapid. Specify a valid parameter value.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

I've checked sysarticles to see if there's an old or orphaned record, but the table doesn't appear. Any ideas as to what the issue is here?

Edit: SQL Server 2016 RTM

TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions and a longer explanation.

Replication uses database triggers for certain things

When a database is published for transactional replication in SQL Server, as part of the set-up process several database-level triggers are created. Normally, they are:

tr_MStran_alterschemaonly
tr_MStran_altertable
tr_MStran_altertrigger
tr_MStran_alterview

You can find them by expanding your database in object explorer, then looking in "Programmability" and then "Database Triggers." They do pretty much what they say. If you were to look at the code in tr_MStran_altertable you'd see that each time this trigger fires for an ALTER TABLE statement, SQL Server uses the EventData() function to capture what's being changed, then calls another replication stored procedure that will actually capture the activity and send the related DDL transaction to the distribution database. This is different than the log reader agent that looks for and captures data changes.

These triggers are created each time you publish a database for replication, and are removed as part of removing replication as well.

New DROP TABLE support in SQL Server 2014 SP2 and SQL Server 2016 SP1

In SQL Server 2014 SP2 and SQL Server 2016 SP1, Microsoft introduced a change for SQL Server replication: Support for DROP TABLE (https://support.microsoft.com/en-us/help/3170123). Before, you couldn't drop a table that was marked for replication. This new feature, which is not enabled by default, means that the DROP TABLE statement will not throw an error anymore. To facilitate this change, a new database level trigger was added: tr_MStran_droptable. This is the trigger that fires for each drop table action, and it uses the event data to determine which table was being dropped, and if the option to support the transaction even though the table is marked for replication is enabled.

But what about "older" versions of SQL Server?

And that's where the problem comes in: this trigger only gets created by replication on versions that support it... and only removed by replication on versions that support it. So even if you decide to nuke replication with something like sp_removedbreplication this trigger won't be dropped, and continue to fire each time you try and drop a table, even if the table you're trying to drop isn't marked for replication. This can happen if:

You have a database published for replication on a version supporting this functionality (either 2016 SP1 or 2014 SP2)
You backup and restore this database to a version of SQL Server that does NOT support this functionality (like 2016 RTM or 2014 SP1)
You publish the restored database for replication.

You can test this yourself: if you disable replication and re-enable it, you'll notice the create times on the database triggers will coincide with when you re-enabled publishing, but the tr_MStran_droptable trigger remains.

Fixing the problem

To solve this problem, you can either:

Upgrade the affected instance to a patch level that supports this trigger (2014 SP2 or 2016 SP1).
Manually delete the trigger, since it isn't supported.


Mysql replication, error 13 (permission denied) with slave_load_tmpdir I've setup a virtual machine on Azure with Ubuntu 16.10 and Mysql 5.7 for a database replication Master/Slave from another server.

I followed this article: https://www.opsdash.com/blog/mysql-replication-howto.html

All seems to works correctly, replication works but I've serious problems with the use of DATA LOAD INFILE. If I use this command, the replication doesn't works.

I tried with relative varible "slave_load_tmpdir", creating a new directory '/var/slavetmp' and updating my.cnf with these values, but this is the error in mysql with "SHOW SLAVE STATUS":

  Unable to use slave's temporary directory /var/slavetmp - Can't read dir of '/var/slavetmp/' (Errcode: 13 - Permission denied)

Have any ideas?

Thanks in advance

Assuming that the MySQL user is called "mysql" (check for "user" in my.cnf):

chown mysql.mysql -R /var/slavetmp
Edit /etc/apparmor.d/usr.sbin.mysqld.  Copy the lines for /var/lib/mysql and edit the copied lines to refer to /var/slavetmp
Restart AppArmor:  /etc/init.d/apparmor restart
Restart the mysql service


Don't see the Microsoft.Jet provider in my new SQL Server 2008 R2 instance? I moved an Excel linked server from SQL Server 2005 (it was built on Jet 4.0, excel 5?) to a new SQL Server 2008 R2 instance (64 bit environment). I matched the settings for both, but it seems the system tables in the Catalogs\defaults.. didn't transfer..

The new server providers list doesn't have the Jet 4 or the Microsoft ACE 12....

How can I install them? hoping that will fix the issue

Thanks in advance,

Shayma

I believe that starting in 2008 R2 Jet was no longer included with the SQL Server install.  For R2 64 bit I believe you will need to download and install the Microsoft Access Database Engine Redistributable package found here.

This should install the necessary drivers for Office doc access from 64 bit SQL Server.


MySQL replication - database size is different I have enabled MySQL master-master replication with 2nd master acting as read-only. Recently I checked the out the size of the databases in both master and slave and I found that database size in two databases is 1mb less than the Master. Does it mean that the setup which I made for master-slave was not done properly or the replication is not happening properly?
Also, how can I make the changes so that it remains the same in both master and slave?

Thanks!

The setup can differ slightly in size and be consistent at the same time. Could be a range of reasons for the size difference. E.g. Different file system, fragmentation, method of determining size, etc...

If you want to test the consistency of your replicated tables, run a pt-table-checksum.


Max Degree of Parallelism for SQL Server 2008 Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and indicates that some of the issues aren't fixed in SQL Server 2008.

Six Reasons You Should Be Nervous About Parallelism

Is there any indication that here are still problems with MAXDOP > 0 and are any DBAs out there not using 0 to reduce risks?

This should be set with respect to your system baseline, you have to balance it with your CPU + COST Threshold for parallelism.

for example, i was using the configuration of maxdop=0 when i realized that i have an high number of worker threads. once i reset it to 5 and cost threshold to 50 everything was fine.


SQL server 2008 Express x64 in windows 8.1 fail I've been download sql server express x64 version in this

I just install new windows 8.1 x64 vl version and install trial KIS for anti-virus. After i install sql server but it's not working

and

i uninstall KIS, SQL server and install avast and sql server again but the same :(.

How to fix that thanks.

You're attempting to use "SQLExpress" as a server name.  I'm guessing that's the instance name.  Connect to ".\SQLExpress" instead.


MySQL Queries to get only runtime/duration, not fetched results We are running few benchmark queries against our test RDS instance for checking efficiency of partitioning, before adapting it to production, where there is no partitions presently.

We have few tables of time series data, and hence partitioning based on RANGE( unix_timestamp(event_time) ). Queries we run against sets of tables(partitioned and non-partitioned ones) which has millions of rows is taking too much time, which we are stuck at the moment.

We see from MySQL Workbench it's quite obviously the fetch time(network dependent) takes up the time. What we are concerned about is only the runtime duration of the queries we run. Is there any way we can skip the fetched results part and only get the runtime duration for respective queries?

Query Examples we try :

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59';

What we do for saving time is with limit the results with,

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59'
    Limit  0,1000000;

which won't match live scenarios.

(I have too many Comments for Comments...)

Please provide SHOW CREATE TABLE.  There are many details left out of the Question.
Please provide EXPLAIN SELECT ....  We need to discuss whether it is really doing any pruning.
How many rows are returned by those queries?
Are they realistic queries -- do you really need all columns; do you really need all rows (versus need to summarize them)?
Depending on the indexes, you may find that partitioning is slowing down the operation, not speeding it up.
Let's see the real queries.
The following avoids computing the end date, leap-years, etc.  And it grabs the missing second.

.

      event_time >= '2018-01-01'
  AND event_time  < '2018-01-01' + INTERVAL 2 MONTH

In my experience,

The only purpose for PARTITION BY RANGE(event_time) is to purge old data.
Summary tables are the real 'fix' for performance problems in log tables.

Once you have addressed most of my comments, the title Question should be moot.


Oracle AWS package errors when removing user We are getting the folowing error when attempting to remove a user from our 11g Oracle. a dump from an Amazon DB was (failed) imported, and now we see this.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04045: errors during recompilation/revalidation of {USER}.RDSADMIN
ORA-04067: not executed, package body "{USER}.RDSADMIN" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"{USER}.RDSADMIN"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
"{USER}.RDSADMIN"
ORA-06512: at line 2

How do we remove this {USER} and it's RDSADMIN packages?

Thanks in advance

C

Ideally if user exists with same name, it doesn't throw this error. Try overriding the error using

  IMPDP with REMAP_SCHEMA option.


Scale MySQL trigger for thousands of inserts per seconds We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256)

A row is added to table token every second using python MySQLdb. I have an after insert trigger on token table which does the following:

Get the latest entry (last) from token-big's
If unix_starttime of newly inserted row (in token) is no more than 5 seconds apart from unix_endtime from token-big:

update unix_endtime of last row in token-big to unix_endtime of newly added row in token
else insert new row in token-big same as the newly added row in token

This works fine for a few token token-big table pairs but I start to get deadlocks if these pairs (tables) are in number of hundreds.

How can I scale this method? Is there any better alternative?

I don't wish to use events as it may delay the data shown to user. Same goes for background jobs which run every few minutes or so.

Don't use a TRIGGER.  Instead, gather a minute's worth (say) of data in a staging table.  Then do the desired updates en masse.  If you need the user to get up-to-the-second information (instead of just up-to-the-minute), have the user's query do that final update also.

To phrase it differently, summarization is much more efficiently done in bulk.

Your statements are confusing on the scale of the problem:  "thousands of inserts per second" versus "a row is added to table token every second".  Which is it?

If serious scaling of inserts and processing, see my blog on staging tables.  That discusses "ping ponging" of a pair of staging tables in order for the speed to be self-adjusting.

To discuss your situation further, please provide SHOW CREATE TABLE and some more details.


Incorrect query results for execution plan, unsure of exact cause of problem We had a query yesterday that was intended to page through results but on the first page of results the query only returned 4 records instead of the expect 25. Changing the query in any way resulted in 25 records which meant to me that it was an execution plan issue. I'm not familiar with a way to view execution plan in production and running the query in sql studio didn't result in same problem, probably due to minor differences.

I read suggestions that such a thing could be caused by a corrupted index. I ran a checkdb on the database and it found no errors. In the end I cleared the execution plans and all has been well.

If it wasn't a corruption issue of some kind and only a problem for the very specific execution plan then does that mean there was simply an error in the execution plan and we are hitting a bug in SQL Server? We are on SQL Server 2012 RTM with no updates so I looked through documentation on all fixes in cumulative updates and service packs but none of the issues appeared to related to our own.

Any other ideas or thoughts as to what could cause this?

(@P1 varchar(8000),@P2 bit,@P3 varchar(8000),@P4 bit,@P5 varchar(8000))

SELECT e.*
FROM (

    SELECT

        TOP 25
        ROW_NUMBER() OVER (
            ORDER BY AddedDate DESC
        ) AS Row,

        ID
        , Prefix
        , FirstName
        , LastName
        , Company
        , Address
        , City
        , State
        , Zip
        , Country
        , WorkPhone
        , HomePhone
        , MobilePhone
        , Email
        , MailingLists
        , AddedDate
        , AwaitingOptin
        , OptInDate
        , Processed
        , ProcessedDate
        , Deleted
        , Source
        , GRRecID
        , DatabaseID
        , (SELECT COUNT(*)
            FROM TableA
            LEFT OUTER JOIN TableB ON TableA.GRRecID = TableB.GRRecID
            WHERE TableA.AccountID = @P1
                AND TableA.Email = TableC.Email
                AND TableA.Deleted = @P2
                ) AS Matches
    FROM TableC
    WHERE AccountID = @P3
        AND Processed = @P4
        AND Deleted = 0
        AND Source = @P5
) AS e
WHERE Row >= 1 AND Row <= 25
ORDER BY Row

I don't believe that result was incorrect at all, your query is ambiguous. Semantically your subquery enumerates all rows and then you pick the top 25 rows without specifying an order by. That means that SQL server is at liberty to return any rows it feels like and those rows do not have to be the rows enumerated 1 to 25. It can be just any random rows and in your case four of the rows was enumerated in the range 1 to 25. The rest was filtered out in the main query.

You can fix the query by removing the top statement in the subquery.

  In your opinion based on the query what would be the best course of
  action?

I would test all the options you have and pick the one that performs the best.

I will give you one more option to test that assumes ID is the primary key column in TableC and that AddedDate is not the clustered key.

Add an index on AddedDate including the columns you need to filter out rows before the enumeration happens.

create index IX_TableC_AddedDate on TableC(AddedDate)
  include(AccountID, Processed, Deleted, Source)

Rewrite your sub query so that the only returns ID and row and then join back to TableC in the main query to fetch the rest of the columns.

SELECT E.Row,
       C.*, -- don't use * here, include the columns you actually need
       (
       SELECT COUNT(*)
       FROM TableA as A
         LEFT OUTER JOIN TableB AS B
            ON A.GRRecID = B.GRRecID
       WHERE A.AccountID = @P1 AND
             A.Email = C.Email AND
             A.Deleted = @P2
       ) AS Matches
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY C.AddedDate DESC) AS Row,
            C.ID
     FROM TableC as C
     WHERE AccountID = @P3 AND
           Processed = @P4 AND
           Deleted = 0 AND
           Source = @P5
     ) AS E
  INNER JOIN TableC as C -- Join back to TableC
    ON E.ID = C.ID
WHERE E.Row >= 1 AND E.Row <= 25
ORDER BY E.Row

There might be an even more efficient index you can try but the datatypes for your parameters are somewhat confusing. Is AccountID really a varchar(8000)? If not you should change the data type to match that of the column and in that case a better index to use would be to lead of with AccountID.

create index IX_TableC_AccountID_AddedDate on TableC(AccountID, AddedDate)
  include(Processed, Deleted, Source)

The same goes for Source depending on what datatype it is. If the datatype appropriate you could add that one as well before AddedDate in the columns list of the index.

You could even use all the columns from the where clause in the index if like to avoid any residual predicate.

create index IX_TableC_ on TableC(AccountID, Source, Processed, Deleted, AddedDate)

Just make sure AddedDate is the last column in the index and that you don't have any columns before AddedDate that is not used in the where clause.

A blog post by Itzik Ben-Gan on the same lookup problem (for offset-fetch):

Avoid Unnecessary Lookups when Using ROW_NUMBER for Paging