Schema of unstructured data can be defined at query time I am looking for an example to understand the following statement:
Schema of unstructured data can be defined at query time

The statement "Schema of unstructured data can be defined at query time" sounds like a little bit of poor choice of words but likely eludes to the point that all data has a structure, even if it hasn't been defined explicitly yet (such as in a relational database management system).
In the context of NoSQL, an unstructured dataset such as JSON may not explicitly define things like the data types of the data itself, but the consuming applications of that data may define the schema of that data explicitly ahead of time such as when defining models to physically represent your data as objects.
For example, TypeScript is a JavaScript framework where you can define class files to store your model definitions that deserialize the JSON data into objects, such as when you receive data back from a restful web service. Furthermore, you can then persist that data into another database system, such as SQLite for example, where the schema is materialized with different (but compatible) data types. TypeScript has the number data type whereas you could store the same data in SQLite as INTEGER or REAL depending on what fits your use case.
The point being, there is no defined schema at the source of the data, but once it's queried and consumed in an application, it is possible to define one, or even multiple schemas that appropriately fit the shape of the data.
Additionally, some applications implicitly define a schema around the data they consume, for example, with an ORM, or with data migration tools. The purpose of an ORM is to literally define concrete models for a particular set of data. While most ORMs usually consume structured data, there are unstructured ORMs as well.


Transaction Log Backups Serial or Parallel? We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance.

This question isn't so much about Ola's scripts as they are about a best practice. I realize the ultimate answer is "it depends on your company's requirements". But I am trying to seek the community's advice on how best to fulfill what I understand of our company's requirements.

I wish to set up transaction log backups for every 15 minutes. This way we hopefully lose no more than 15 minutes of data. Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I ask, because I have the feeling based on how I see Ola's script functioning that the backups are kicked off in serial. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case. I would want the others to continue backing up.

So is it true that Ola's scripts execute in serial and also a failure stops successive backups?

And is it better to have a job for each database? or a single job that does all? My inclination is toward separate jobs, but I wish to understand what SQL Server DBA's in general tend to do.

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel?

I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.

What could be possible drawbacks with running in parallel

Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job.
Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you.

  The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes).

Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you.

  Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case

I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be

The owner running the job is removed from AD
Someone changed the recovery model of database.
Insufficient disk space

Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.


SSMS - Is it possible to pass additional connection parameters via Command Line? I'm able to launch SQL Server Management Studio (2014) from command line using something like below.

ssms.exe -E -S myservername -D -mydatabasename

However, I'm not sure how to pass additional connection parameters such as multisubnetfailover=true

Is it possible to do this via commandline or through 'Target' on SSMS windows desktop properties?

Unfortunately not. If you type ssms.exe -? or look at the documentation you'll see the list of possible options:

  Ssms
      [scriptfile] [projectfile] [solutionfile]
      [-S servername] [-d databasename] [-U username] [-P password]
      [-E] [-nosplash] [-log [filename]?] [-?]

So there is no option to specify a connection string or additional connection properties.


How to optimize constant querying for a sum of many rows? I'm making an app that is supposed to view election results (yep, homework assignment). For each of the 24681 wards and each of the 12 candidates the number of votes the said candidate has received in the said ward is to be stored.

The most common operation, of course, would be viewing voting results aggregated over all election wards. Also there must be separate tables for each province, electoral district and municipality. Viewing aggregate voting results for each of these areas must also be possible.

Aggregating election results over the whole country takes up time measured in seconds. I consider this inappropriate even despite it's only a homework assignment. If this app was to be exposed to general public it would be unusable. I think it is broken to require aggregating results over 24681 wards each time a user loads the main page!

One way to counter that would be to store aggregated results for each territory (ward, municipality, province, district, whole country) in the database. This, however, would break normal forms pretty badly and I'm not sure if that's acceptable.

I'm thinking about database views. I'm imagining that the database would cache the results of views just as if I would break normal forms by adding relevant tables and columns myself and automatically recreate this cache for each larger area each time election results for the smaller area are modified (yes, editing the number of votes for each candidate in each ward is required). Can I make views work that way?

How else can I combat this issue?

The concept you want is called a "materialized view". Normally, a view is fundamentally just a query prepared in advance, that you can reference like a table for convenience. A materialized view is at least partially stored in the database.

In SQL Server, this is done by creating an indexed view. This acts as an index tied to the table(s) in the view, and is updated when the underlying rows are updated, just like any other index. Note that the SQL Server Developer's Edition is free, and has very few limits on it.

Near as I can tell, neither MYSQL nor PostGreSQL provide this feature in this form.

With such a materialized view, you still have a potential for a performance issue. Since the index is updated every time the underlying rows are updated, if you're trying to tally up votes in real time, you may find that the view is constantly being modified. Between frequent SELECTs against the view, and constant INSERTs and/or UPDATES on the underlying tables, you may face blocking issues, a major problem if it prevents you from showing results, and an even worse problem if it prevents votes from being entered in the first place!

You may want to think about another option. Consider if you need a real-time tally, or if one that's been updated recently (say, within the last 5 minutes) would be sufficient. From what I've seen in election results in the US, that's the path they seem to take.


What do you call something that's not the primary key? I've been working with a group who have built a system where they've coined a few new terms in the process, and I wanted to know if there's something more standardized they should be calling it.

Basically, they have tables which hold versioned info, so the unique key for the table is the identifier for the document being tracked, plus the record number (although, the record number is auto-numbered, so it's unique in itself).  They refer to the fields that make up the document's identifier (it changes for each table), as the 'prime key' ... but the fields both individually and collectively (eg, if it has fields A, B and C to get a unique identifier, they might interchangable refer to A as being 'a prime key' and A/B/C as being 'the prime key'

If they had a normalized database, this would be an obvious candidate for a foreign key -- but it's not normalized, so it's not a reference to a different table.

Is there some name for this concept -- an identifier to select some concept that's not what the table is based on, but which isn't a foreign key?

update: it's not an alternate key, as it doesn't identify a unique record within the table; it identifies a group of records.  Take the folowing table:

id   document_id   version_no
--   -----------   ----------
 1             1            1
 2             2            1
 3             1            2
 4             2            2
 5             2            3

So we're tracking metadata for 5 objects, which are two different files, one of which has been updated once (2 versions/editions of it), and one which has been updated twice.

We have the following:

candidate keys : (id) or (document_id, version_no)
natural key : (document_id, version_no)
surrogate key : (id)
primary key : (id, document_id)  (I didn't select it)

... if document_id were a relationship to another table, it'd be a foreign key, but 'foreign key' typically also infers a constraint (ie, the value has to exist in some other table before it can be used here) ... but in this case, it's not.  So, is there some other term to describe this as something that selects a related grouping of objects rather than a single record out of the table?

The first definition of surrogate on the wikipedia page for surrogate key fits, but that's not the common usage of the term, where most people consider a surrogate key to fit the second definition:

Surrogate (1) : This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.
Surrogate (2) :
This definition is based on that given by Wieringa and De Jonge (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

update 2 : Basically, if you had a table that handled versioning of records, if you moved the records to a new table and stored editing history, the fields that link back to the original table would be considered a foreign key ... but if you stored all of it in one table, without relating to a secondary table, is there a name for the group of fields that identify the multiple versions of the record?

The reason I ask is that the group I'm working with has decided to call it a 'prime key', and when I came on board, I reviewed all of their documentation, and told them they had a typo, and it was the 'primary key', but they explained that no, it was the phrase they had coined to describe this concept.

I'm trying to identify if there's a accepted name in the database community for this concept.

User terms surrogate key and natural key for primary key as a variant.

  Surrogate Key:

  Surrogate keys are keys that have no
  “business” meaning and are solely used
  to identify a record in the table.
  Such keys are either database
  generated (example: Identity in SQL
  Server, Sequence in Oracle,
  Sequence/Identity in DB2 UDB etc.) or
  system generated values (like
  generated via a table in the schema).

  Natural Key:

  Keys are natural if the attribute it
  represents is used for identification
  independently of the database schema.
  What this basically means is that the
  keys are natural if people use them
  example: Invoice-Numbers, Tax-Ids, SSN
  etc.

Surrogate Keys vs Natural Keys for Primary Key


How can I combine a large CROSS JOIN query and return 1 result? I have a rather large PostgresSQL query that returns a single JSON result that is then sent to the front-end app for display:

Select * FROM (SELECT sum(subscribers) AS subscribers FROM plans WHERE userid=$1 AND startDate>=$2 AND startDate<$3) subscribers
                CROSS JOIN (SELECT count(status) AS inactivesubscribers FROM subscribers WHERE userid=$1 AND status='inactive' OR status='suspended') inactivesubs
                CROSS JOIN (SELECT count(planName) AS plans FROM plans WHERE userid=$1 AND status='active') plans
                CROSS JOIN (SELECT count(promoCode) AS promotionals FROM promotionals WHERE userid=$1 AND status='active' AND startDate>=$2 AND startDate<$3) promotionals
                CROSS JOIN (SELECT count(chargeDate) AS charges FROM transactions WHERE userid=$1 AND chargeDate>=$2 AND chargeDate<$3) charges
                CROSS JOIN (SELECT count(refundDate) AS refunds FROM transactions WHERE userid=$1 AND refundDate>=$2 AND refundDate<$3) refunds
                CROSS JOIN (SELECT count(sentDate) AS messages FROM messages WHERE userid=$1 AND sentDate>=$2 AND sentDate<$3) messages
                CROSS JOIN (SELECT count(templateName) AS activetemplates FROM templates WHERE userid=$1 AND status='active') activetemplates
                CROSS JOIN (SELECT count(templateName) AS inactivetemplates FROM templates WHERE userid=$1 AND status='suspended') inactivetemplates

Returns:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4'
  }
]

But, I'm not sure how I can add this query to gather 6 of the most popular plans within the large query:

CROSS JOIN (SELECT planName,subscribers as popularplans FROM plans WHERE userid=$1 AND status='active' ORDER BY subscribers DESC LIMIT 6) popularplans

Due to the CROSS JOIN instructions, the query above doesn't work as intended, because the query creates 6 different object results with planname: plan x and popularplans: x on each found row.

[
      {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 1',
        plans: '12',
        popularplans: '22',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4'
     },
     {
        subscribers: '146',
        inactivesubscribers: '12',
        planname: 'Plan 2',
        plans: '12',
        popularplans: '12',
        promotionals: '12',
        charges: '7',
        refunds: '12',
        messages: '5',
        activetemplates: '4',
        inactivetemplates: '4'
     }
     ...etc
 ]

Ideally, I want the large query to return a single JSON object and be structured like so:

[
  {
    subscribers: '146',
    inactivesubscribers: '12',
    plans: '12',
    popularplans: [
      { planName: 'Plan 1', subscribers: '22' },
      { planName: 'Plan 2', subscribers: '12' },
      ...etc
    ],
    promotionals: '12',
    charges: '7',
    refunds: '12',
    messages: '5',
    activetemplates: '4',
    inactivetemplates: '4'
  }
]

Is there a better or a different approach to the large query than tons of cross joins?

Try this using jsonb_object_agg(), and jsonb_build_object(k,v)

CROSS JOIN (
  SELECT json_agg(jsonb_build_object('plan', planName, 'count', subscribers) ORDER BY subscribers DESC) as popularplans
  FROM (
    SELECT *
    FROM plans
    WHERE userid=$1 AND status='active'
    FETCH FIRST 6 ROWS ONLY
  )
) popularplans


Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard? MySQL 5.1.x | Windows Server 2003

Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue?

For example, if I set concurrent connections to 800 in the dialogue window, I see max_connections=1023 in my.ini.  Why? Is this normal?

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

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

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


Retreive data from two tables of the same structure I have a SQLite database where I fetch data from two tables with the following query.

select ie.*
from (select *
      from History where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
      union all
      select *
      from Pending where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
     ) ie
order by TimeStampCome desc LIMIT 100 OFFSET 1

Is this the best and most efficient way? I have stored alarm objects in the database. So there can easily be more than 1 million entries.

The outer-level ORDER BY will probably cause the entire combined row set to be sorted – at least to the extent that will enable the server to find the last 100 TimeStampCome entries starting from the penultimate one. Sorting a million entries cannot be cheap, of course.

I would try reducing the combined set by applying a LIMIT to each leg of the UNION ALL query. Note that an ORDER BY cannot be applied directly to an individual SELECT that is a UNION leg. You will have to use more nesting – either a derived table or a CTE – to apply the ORDER BY and then select from that derived table/CTE. Here is a derived table solution:

SELECT
  ie.*
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          History
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
    UNION ALL
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          Pending
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

And this is a variation that uses CTEs, if you are using a SQLite version that supports Common Table Expressions:

WITH
  HistoryFiltered AS
  (
    SELECT
      *
    FROM
      History
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  ),
  PendingFiltered AS
  (
    SELECT
      *
    FROM
      Pending
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  )
SELECT
  ie.*
FROM
  (
    SELECT * FROM HistoryFiltered
    UNION ALL
    SELECT * FROM PendingFiltered
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

Each individual SELECT is using LIMIT 101 rather than LIMIT 100, because at first it is unknown which of them will end up having the latest TimeStampCome entry that will be omitted by the outer OFFSET 1. Therefore, each source row set must provide as much data as necessary for the ultimate SELECT.


postgresql - trouble to order a list obtained by string_agg I obtained a list of numbers (and text) by a string_agg.

For instance, that :

"09,12,20,200,217,400,500,90,91,92,Exp3"

This is the string_agg:

string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes

My trouble with the ordering: I need an alphanumeric order like:

"09,12,20,90,91,92,200,217,400,500,Exp3"

If I try a CAST, it refuses to accept text "numbers" like "Exp3".

So I didn't find any way to order it like values with text objects at the end.

EDIT
I don't know how to use WITH in my query without cutting my results!
In fact, this is the total query:

SELECT l_arret_ligne.idap,
       t_arret.nom_arret AS nom,
       string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes
FROM l_arret_ligne,
     t_ligne,
     t_arret
WHERE l_arret_ligne.id_ligne::text = t_ligne.id_ligne::text
  AND l_arret_ligne.idap::text = t_arret.idap::text
  AND t_ligne.ligne_type::text <> 'Scolaire'::text
GROUP BY l_arret_ligne.idap, t_arret.nom_arret

As my string_agg is inside a global query, I don't understand how it would be possible to cut it with WITH and globalize it at the end.

Maybe do I understand bad...

Thanks again!

EDIT 2:
Hello again!
I found one very good solution to order my results:

SELECT string_agg("rla"."t_ligne_19032019"."ligne_code", ',' ORDER BY case when (UPPER(ligne_code)=LOWER(ligne_code))=true then substring(concat('000000000000000',ligne_code),length(ligne_code)+1,15) else ligne_code end) AS lignes
FROM "rla"."t_ligne_19032019"

It works very well. BUT always the same trouble with DISTINCT!
If I add DISTINCT in the string_agg, same message:

  ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must
  appear in argument list

Any solution? Any advice?

Thanks again!

Cyril

The order by in a string_agg() can absolutely use a CASE expression:

with data (ligne_code) as (
  values
  ('a value'), ('09'), ('12'), ('20'), ('007'),
  ('200'), ('92'), ('11'), ('Foo'), ('Exp3')
)
select string_agg(ligne_code, ',' order by case when ligne_code ~'^[0-9]+$' then ligne_code::int end, ligne_code)
from data;

returns:

007,09,11,12,20,92,200,a value,Exp3,Foo

The expression when ligne_code ~'^[0-9]+$' checks if ligne_code consists of numbers. If that is the case it's converted to an integer which is the first sort criteria. All non-numbers come after that and are sorted in a alphabetic way.


Confused about load balancing and horizontal scaling in postgresql Please correct me if I am wrong but I guess handling more requests and load by adding more machines or balancing the load between multiple servers is horizontal scalling. So, if I add more servers, how do I distribute the database? Do I create one database to hold the user records with multiple servers? Or do I split the database too? What about database integrity? How to synchronize it? Or else what do I do? I am a newbie and really confused but eager to learn. I would like to use postgres for my project and would like to know some basic things before I start. I was thinking of using two small ec2 instances. But I got confused about the database. How do I go about creating the database. Do I need to go through sharding for this? What would be the best approach for horizontal scaling in accordance to postgres. I would really appreciate if you could explain it to me. Thank you!

Edit:

How do I load balance using multiple machines and manage the database?

I have an app where users can upload videos and it will be converted to mp4 using Elastic Transcoder. Users about 10k. So, how do I load balance using multiple machines and manage the database? What I want to do is load balancing for the performance. And I read in many post that adding more machine can leverage it. So I though of horizontal scaling. But since horizontal scaling is scary, how do I load balance and manage my database?

First rule of horizontal scaling of a database is to avoid it. At all cost. You should consider it only when no server you can possibly buy can handle your data. And there are servers which can handle enormous amounts of data today.
Horizontal scaling of a database will give you:

at least an order of magnitude more complicated system:

even in a simplest case of sharding you'd have to manage more servers, distribute load evenly, manage schema updates and shared tables in all nodes, make your frontend connect to correct server etc.;
if you use replication instead, then you'd have to manage replication delay, failing queries on a replica, secure transfer of data between primary and replicas, replicas going out of sync, updates have to be run against master etc.;

at least an order of magnitude more bugs, direct consequence of above complications;

at least an order of magnitude harder to debug bugs, direct consequence of above complications;

at least 2 times higher probability of hardware failure - you'd have at least 2 servers, probability of any of them failing is higher than one specific failing;

probably an order of magnitude worse performance than a single server for the same price, as better storage options like battery backed drive controller or SSDs, partitioning your data, smart placement of WAL files, bigger cache etc. would be far more important than buying another, slow server; remember that databases are mostly bottlenecked by storage.

You can scale horizontally clients connecting to a database (frontends are mostly bottlenecked by processor, so this makes sense), you should use replication for failover (so called warm standby server), you can partition your data within the same database (which would get you most of benefits of sharding with far less drawbacks). Just don't do horizontal scaling before you're of a comparable size to Google, Twitter or Facebook. So no 2 small EC2 instances, no.


GRANT EXECUTE ON PROCEDURE unable to USE database I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance.

The stored procedure is defined with SQL SECURITY as DEFINER.

As a user with permission to create a stored procedure

USE dataDB;
DROP PROCEDURE IF EXISTS fetchData;

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY DEFINER
  COMMENT 'Accepts an ID and returns the record'
BEGIN
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

########
GRANT EXECUTE ON PROCEDURE `dataDB`.fetchData TO 'otherUser'@'%';
FLUSH PRIVILEGES;

########

As 'otherUser'@'%'

USE dataDB;
/* SQL Error (1044): Access denied for user 'kny_opea'@'%' to database 'kny_mint_pre' */

CALL dataDB.fetchData(3);
/* SQL Error (1370): execute command denied to user 'otherUser'@'%' for routine 'dataDB.fetchData' */

I then granted the following

GRANT EXECUTE ON `dataDB`.* TO 'otherUser'@'%';

But this becomes an issue as the user then has execute permissions to any other existing procedure and any future procedures within dataDB.

How do I grant execute permission to a single procedure to the invoker without granting execute privilege to all procedures or another privilege that would allow the user to USE the database?

SUGGESTION #1

Check the user in the Stored Procedure and leave if it is not the right user

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY DEFINER
  COMMENT 'Accepts an ID and returns the record'
ThisStoredProcedure:BEGIN
  SET @cur_user = CURRENT_USER();
  IF LEFT(@cur_user,LOCATE('@',@cur_user) - 1) = 'otherUser' THEN
    LEAVE ThisStoredProcedure;
  END IF:
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

SUGGESTION #2

Create New Proc with SQL Security to Invoker and change grants of otherUser to match

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchDataForOthers`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY INVOKER
  COMMENT 'Accepts an ID and returns the record'
BEGIN
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;


Multiple BindIp in MongoDB 3.4.1 on Debian Jessie I just upgraded from MongoDB 3.2 to 3.4.1.

/etc/mongod.conf had a multiple BindIP:

bindIp: [127.0.0.1,192.168.20.1]

This is not working anymore.
I get this error :

Scalar option 'net.bindIp' must be a single value

According to the documentation, It must be correct :
https://docs.mongodb.com/manual/reference/configuration-options/#net.bindIP

Any idea ?

Remove []. so solution is: bindIp: 127.0.0.1,192.168.20.1 and remember, no space characters anywhere in that string.


Why is my Target Server Memory slightly higher than my Total Server Memory? I'm doing a performance test on a high traffic (16000+ users, estimated 1000+ concurrent at peak) Microsoft Dynamics database.
The server has gone on line a few months ago, and I'm doing some tests to see how it is handling the current traffic.

While checking some performance counters during business hours I came across an oddity in the target and total server memory.
While I would only flag this as being an issue if the target memory was clearly higher than the total memory, combined with perhaps other counters (depending on the amount of difference) the results here are somewhat odd.

Using:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters
where (object_name like '%Buffer Manager%' and counter_name like '%page life%')
or (object_name like '%Memory Manager%' and counter_name like '%memory grants pending%')
or (object_name like '%Memory Manager%' and counter_name like '%target server memory%')
or (object_name like '%Memory Manager%' and counter_name like '%total server memory%');

I came to the following results:

counter_name                 |    cntr_value
------------------------------------------------
Page life expectancy         |    14887
Memory Grants Pending        |    0
Target Server Memory (KB)    |    44040192
Total Server Memory (KB)     |    44040184

Clearly there is currently no memory pressure, and the page life expectancy is higher than required. However it looks like at some point the sql server was exactly 8 KB short of memory.

Something about these numbers seems odd to me, but I can't put my finger on it.
Is this an indication that at some point the sql server was on the verge of a memory problem?
Am I correct in thinking that because these numbers are so similar, I can dismiss it for now, and test again when a decent amount of time has passed?

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below

  Target1 = Current committed pages of SQL Server + ( Available Physical
  Memory - min (Total Physical Memory Pages / 20, Available Physical
  Memory Pages / 2))

  ullAvailPageFile: The maximum amount of memory the current process can
  commit, in bytes. This value is equal to or smaller than the
  system-wide available commit value. To calculate the system-wide
  available commit value, call GetPerformanceInfo and subtract the value
  of CommitTotal from the value of CommitLimit.

  If (Max Server Memory < ullAvailPageFile)

  {

  Target2= Max Server Memory

  }

  Else

  {

  Target2=Total Physical Memory

  }

  Target Server Memory (KB) =Minimum (Target1,Target2)

  So if AvailablePhysicalMemory is very high (or) when MaxServermemory
  is low then Target Server Memory (KB) would give you the
  MaxServerMemory  else value derived from above formula.

As per my findings using this formula even if target server memory is bit greater than total it is fine. And this is by virtue of how the value is calculated.

If target server memory is greater that total server memory it does not always signify a memory pressure. You must come out of this belief(if you have one). Like you mentioned other counters have to be taken into picture. SQl Server changes its target and total server memory values many a times and this depends on load on system. Consider a scenario where you run index rebuild for all big tables there would be flurry of activity and PLE would certainly drop increasing target memory but this does not means it is a memory pressure. SQL Server memory just adjusted itslef to current load.

In ideal world you should have RAM such that it can shold all databases in memory but this is many a times not possible.


How to check the timezone for a given datetime? A database / table I'm working it saves all its date values as datetime (without a timezone).

How can I find out which timezone that date was in?

For instance, the Central European Summer Time (+02) starts at 29th of March 2015 and the previous Winter Time (+01) started at 26th of October 2014.

Let's say I'm seeing a date of 2014-11-06, is there a function I can use to check to which timezone it belongs?

NB I know that there will be edge cases for when the time zone changes

I'm happy with some big CASE WHEN statement if that what it takes, but of course rather use some system function.

This is neither technically nor even conceptually possible, due to the nature of what timezones are.

It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since:

several timezones can share the same offset
timezones can come and go and can even shift their offset
different regions can change their timezones

Timezones are determined by governments. For example, in the U.S.A., there are primarily 4 timezones for the contiguous 48 states. But those same offsets are also used by other regions that probably have different laws regarding when Daylight Savings starts and ends, or if they even have a Daylight Savings period. And in fact, there are two extra timezones for the contiguous 48 states to handle differences for 2 states: one for Indiana and one for Arizona.

Daylight Savings start and end times change over time. The Indiana and Arizona links above discuss this for those two states. In the United States, the DST start and end dates changed in 2007, but this didn't affect other regions using those same offsets. And the DST offset isn't always 1 hour (mentioned in the prior link).

So, with:

a DATETIME value alone you have no basis for determining either the Offset or the Timezone (even if you had a Timezone database)
a DATETIME value and an OFFSET--whether stored separately or combined in a DATETIMEOFFSET datatype--you have the OFFSET (obviously) but cannot determine the exact Timezone, though it could be narrowed down to a few, but only if you had a table of which Timezones participated in that Offset at that particular moment in time (see below for sources of such data). With only the Offset, the DATETIME value can be converted to UTC, but not to another Offset without first having a Timezone database and knowing which specific Timezone you are converting it to, which will indicate the Offset for that particular point in time. (By saying it cannot be converted to another Offset, I mean in any meaningful way; clearly the Offset value can be adjusted, but that doesn't help much if you don't know what to adjust it to)
a DATETIME value and a Timezone (name) then you can determine the Offset by looking up what the Offset was at that point in time (see below for sources of such data)

Unfortunately this Timezone data is not built into SQL Server. But you can get it from the following sources (and possibly others):

Internet Assigned Numbers Authority (IANA)
TimeZoneDB


Replication not updating XML Schema collection So the problem I'm facing is that replication from our Main instance to our Data Warehouse instance is failing because it isn't picking up XML schema updates. Thus, when it tries to bulk copy the data from the Main table that utilizes it, it fails because XML validation fails due to the XMLSchemaCollection not containing any of the updates.

The behavior that we've observed in the last couple months is that if the XML schema is new, then replication picks it up. However, from that point forward, it ignores it. This smells like a deficiency in the way it detects updates. But I'm hoping that someone else has figure this out.

According to Publish Data and Database Objects in the section Considerations for Publishing - Limitations on Publishing Objects:

  XML schema collections can be replicated but changes are not
  replicated after the initial snapshot.

I agree, this appears to be a deficiency.

As a workaround, you could perform the XML schema update on the Publisher and then post the XML schema update to Subscribers using sp_addscriptexec.


Replace part of strings in multiple columns and tables I have several fields in db containing url inside different strings, part of which I need to replace with another one.
F.e. the string is

abc::http://localhost:5000/pic/img/1.jpg|def:::blahblahblah_http://localhost:5000/hij/klm/hxyhgbcbxjgxz.jpeg:jgfgv/hgh/

I have many different strings like that.

I need to replace http://localhost:5000 with https://example.com but leave everything else untouched.

Also I need to do that without stopping the database / on a working database.

I was thinking about doing smth like that:

UPDATE table01
  SET field01 = replace(field01, 'http://localhost:5000', 'https://example.com')

How can I make this query include all strings in db, not only in table01 & field01, but all tables & all fields? Just want to make sure that there are no references left to this url at all.

First of all, your UPDATE without WHERE clause is needlessly (very!) expensive. It would write a new row version for all rows, even where nothing changes. Only update rows that actually need an update!
UPDATE table01
SET    field01 = replace(field01, 'http://localhost:5000', 'https://example.com')
WHERE  field01 ~ 'http://localhost:5000';

Related:

How do I (or can I) SELECT DISTINCT on multiple columns?

Next, careful what you replace. You'll want to avoid false positives. Test before you apply it to the whole database. Your current expression:
replace(field01, 'http://localhost:5000', 'https://example.com')

would also change strings that probably shouldn't be changed. Like 'http://localhost:50000/pic/img/1.jpg' (note: 50000 instead of 5000)
To avoid that particular case:
regexp_replace(field01, 'http://localhost:5000\M', 'https://example.com', 'g')

Using regular expressions instead. \M at the end of the string, per documentation,

matches only at the end of a word

You may want to do more, depending on your exact requirements. So:
UPDATE table01
SET    field01 =  regexp_replace(field01, 'http://localhost:5000\M'
                                        , 'https://example.com', 'g')
WHERE  field01 ~ 'http://localhost:5000\M';

Then apply to all string columns in a table. Ideally, only update each row once, to make this as cheap as possible. This plpgsql function executes the update for one given table:
CREATE OR REPLACE FUNCTION f_replace_everywhere(_pattern text, _new_string text, _tbl regclass, OUT updated_rows int) AS
$func$
DECLARE
   -- basic string types, possibly extend with citext, domains or custom types:
   _typ  CONSTANT regtype[] := '{text, bpchar, varchar}';
   _sql  text;
BEGIN
   SELECT INTO _sql     -- build command
          format('UPDATE %s SET %s WHERE %s'
               , _tbl
               , string_agg(format($$%1$s = regexp_replace(%1$s, $1, $2, 'g')$$, col), ', ')
               , string_agg(col || ' ~ $1', ' OR '))
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = _tbl            -- valid, visible, legal table name
      AND    attnum >= 1                -- exclude tableoid & friends
      AND    NOT attisdropped           -- exclude dropped columns
      AND    NOT attnotnull             -- exclude columns defined NOT NULL!
      AND    atttypid = ANY(_typ)       -- only character types
      ORDER  BY attnum
      ) sub;

   -- Test
   -- RAISE NOTICE '%', _sql;

   -- Execute
   IF _sql IS NULL THEN
      updated_rows := 0;                         -- nothing to update
   ELSE
      EXECUTE _sql
      USING _pattern, _new_string;

      GET DIAGNOSTICS updated_rows = ROW_COUNT;  -- Report number of affected rows
   END IF;
END
$func$  LANGUAGE plpgsql;

Call to apply to a single table:
SELECT f_replace_everywhere( 'http://localhost:5000\M'
                           , 'https://example.com'
                           , 'my_table');

Closely related with detailed explanation:

Replace empty strings with null values

Finally, apply to all relevant tables in our database. Don't touch system tables. You can loop through all tables based on pg_tables or information_schema.tables. See:

How to check if a table exists in a given schema

I'll leave that last step to you.


insert multi on a table handled by trigger I have this table

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `md5_email` char(32) NOT NULL,
  `entered` date DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `country` varchar(5) NOT NULL DEFAULT 'DE',
  PRIMARY KEY (`uid`),
  KEY `md5_eml` (`md5_email`),
  KEY `entered` (`entered`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (uid)
(PARTITION id50k VALUES LESS THAN (50000) ENGINE = MyISAM,
 PARTITION id100k VALUES LESS THAN (100000) ENGINE = MyISAM,
 PARTITION id150k VALUES LESS THAN (150000) ENGINE = MyISAM,
 PARTITION id200k VALUES LESS THAN (200000) ENGINE = MyISAM,
 PARTITION idOthers VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

& this trigger to check duplicate emails

DELIMITER $$
CREATE TRIGGER checkExistEmail BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
 SET NEW.md5_email = MD5(TRIM(NEW.email));
ELSE
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email Already Exists';
END IF;
END $$
DELIMITER ;

the trigger works fine with single insert cmd

but with multi insert it stops when the first duplicate email found

how can i make it ignore duplicates without aborting all operation ?

  mysql  Ver 15.1 Distrib 10.1.21-MariaDB

You have a contradiction -- On the one hand, you want to "ignore" dups.  On the other hand, you want to "SIGNAL" about dups.  Which is it?

If you want to signal after trying to insert multiple rows, you won't know which row(s) were dups.  If that is OK, then consider something like (I'm not sure of syntax):

DECLARE _dups INT DEFAULT FALSE;
FOR EACH ROW
  BEGIN
  IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
    SET NEW.md5_email = MD5(TRIM(NEW.email));
  ELSE
    SET _dups = TRUE;
  END IF;
  IF _dups THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Some Emails Already Exists';
  END IF;
END $$

Alternatively, you could probably use rows_affected() to see how many were successfully inserted.


VACUUM VERBOSE outputs, nonremovable "dead row versions cannot be removed yet"? I have a Postgres 9.2 DB where a certain table has lots of nonremovable dead rows:

# SELECT * FROM public.pgstattuple('mytable');
 table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 2850512896 |      283439 | 100900882 |          3.54 |          2537195 |     2666909495 |              93.56 |   50480156 |         1.77
(1 row)

Normal vacuuming also shows lots of nonremovable dead rows:

# VACUUM VERBOSE mytable;
[...]
INFO:  "mytable": found 0 removable, 2404332 nonremovable row versions in 309938 out of 316307 pages
DETAIL:  2298005 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.90s/2.05u sec elapsed 16.79 sec.
[...]

The table only has around 300.000 actual data rows, but 2.3 million dead rows (and this appears to make certain queries very slow).

According to SELECT * FROM pg_stat_activity where xact_start is not null and datname = 'mydb' order by xact_start; there is no old transaction accessing the database. The oldest transactions are some minutes old and haven't modified anything on the table yet.

I've also checked select * from pg_prepared_xacts (to check for prepared transactions) and select * from pg_stat_replication (to check for pending replications), both of which are empty.

There are lots of inserts, updates and deletes performed on that table, so I can understand that lots of dead rows are being created. But why aren't they removed by the VACUUM command?

The oldest transactions are some minutes old and haven't modified
  anything on the table yet.

That's not sufficient. I think what is required to mark these rows as dead is that, when these transactions were started, there was no other transaction that had touched these rows (doing an UPDATE or DELETE on them).

Updating or deleting a row will keep the previous version of the row physically where it was, and set its xmax field to the TXID of the current transaction.
From the point of view of other transactions, this old version of the row is still visible if it is part of their snapshot. Each snapshot has an xmin and xmax to which the xmin and xmax of the row versions can be compared.
The point is that VACUUM must compare row versions against the combined visibility of all live snapshots, as opposed to simply checking if a row change is definitely committed. The latter is necessary but not sufficient to recycle the space used by the old version.

For example, here's a sequence of events such that VACUUM can't clean up dead rows even though the transaction that modified them has finished:

t0: Long running transaction TX1 starts
t0+30mn: TX2 starts and sets itself in REPEATABLE READ mode.
t0+35mn: TX1 finishes.
t0+40mn: pg_stat_activity shows only the 10-mn old TX2
t0+45mn: VACUUM runs but won't eliminate the old versions of the rows modified by TX1 (because TX2 might need them).


Transactional Replication data during rebuild To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/apps/reports are reading from an incomplete dataset while the reporting db is being rebuilt.

Does anyone else experience this problem? What is the best work around?

Is #2 in the image a reasonable solution? Create a shell database on top of a data repo. The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline to prevent users/apps/repos from reading incomplete data?

The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline

Yes, that's reasonable.  You can further optimize this approach by pointing the users to a snapshot or restored copy of the subscriber database during the downtime, eg per https://techcommunity.microsoft.com/t5/DataCAT/Microsoft-SQL-Server-Database-Snapshots-and-Synonyms/ba-p/304987, or by reinitializing the subscription from a backup, which might be faster than using a snapshot.

Or you could just disable the reporting users logins and kill their sessions during the downtime.


Is it ever ok to not test a feature? Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before implementing it in your system (especially concerning a feature that modifies data)? Or is it always essential to test a new query by simulation in a test environment?

To specify further, it is clear that it is always safest to test. However, is there a way to determine when the risk is so minimal that testing is not worth the effort? Another way of phrasing that: when or is it ever professional practice to take a measured risk to implement a feature?

Also, let's assume that everything is backed-up, so, worst-case scenario, the data could with some effort be restored.

Can someone cite specific, expert experience to address this? Please include references where appropriate/possible.

I want to start by saying everything I do is SQL Server so those are the examples I give.  In general however this applies to any form of code regardless of system.

Let's start by breaking this down a bit.

Upgrades

You have a system and are about to upgrade some or all of it.  For example upgrading an instance from SQL Server 2012 to 2014.  At this point testing is essential.  Unfortunately testing every part of even a small application is probably not going to be possible.  At that point I would do what I would call a "working" test.  Is the basic system working.  Run through your common tasks start to finish.  Don't test every option, just the main path.

When doing a SQL Server upgrade there is also some required reading.  Basically you want to read the Backward Compatibility entry for the new version (here is the 2014 one) and make sure that you don't have anything in any of the lists (breaking changes, behavior changes etc).

Application Code

Here we are looking at new/changing application code (because of course anything existing has already been tested right?).  In this case everything should be tested.  You should have test cases set up ahead of time and run through at least the majority of your affected features.  Preferably at this point you should also have someone else do a similar check.  This code is going to be in place, probably for a fairly long time, and used by a large number of people.  You want to make sure it works and works well.

One of the things that can really help with this is to generate a set of unit tests that are easily repeatable.  Steve Jones recommends using tSQLt for testing your TSQL code (SQL Server only I'm afraid).  But by doing this you can run through a fixed set of tests quickly and it will really aid in regression testing (testing everything, say before doing an upgrade).

Features/Configurations

Even more than application code changes you want to test new features and configuration changes thoroughly.  If for example you decide to start working with columnstore indexes for the first time you will need to test every piece of code that touches the affected tables.  Use the unit tests that you generated to test your application.  These features are probably new to you (and possibly new in the platform) and will probably have some gotchas you didn't expect.  As for configuration changes, you are talking about something that can affect your whole system, possibly significantly.  The rule of thumb is to test, and test carefully.  There are some changes that you won't really see until you get into an active system (possibly only your production system) but that isn't an excuse not to try them in a test environment first.

Ad hoc queries that reference/affect user data

When you have code that affects your user data you generally need to test it, even, and perhaps especially, because it's Ad Hoc.  Now that being said if you are running the same piece of code, over and over again, just with different parameters, then  you probably don't need to worry about testing each time.

For example you need to delete one or more Ads from the AdList table every quarter.

DELETE FROM AdList WHERE AdName IN ('January 2015 Ads','February 2015 Ads','March 2015 Ads')

At that point you've already tested the code (you are just changing fixed strings) and are probably fairly safe just running the code (assuming you have good backups just in case).

One easy way to test a DELETE, UPDATE or INSERT is to change them to a SELECT and run them, then confirm that the number and type of rows you expect are returned.

You might think that you don't need to test SELECTs because they don't actually change any data.  However you are running the code for a reason right?  Let's say you are doing research for your manager, who will in turn hand this data to their manager and so on.  You test to make sure that you aren't getting the wrong data (or blocking others from collecting their data).

Ad hoc queries that reference/affect system data

This is possibly the one exception to the "test everything" rule.  You are running information queries on system data.  The important thing here is to get back the data you expect.  If the query is something simple (querying a system view) then you are probably ok as long as you've checked what the view/columns really mean.  If the query is complex (say hitting 3 or 4 system views with calculations on the columns returned) then you may want to run a few tests just to make sure you are going to get back the data you expect.

Summary

In summary, yes, you want to test everything.  If it's important enough for you to write it and run it then it's important enough for you to test.  That doesn't mean you have to spend enormous amounts of time testing every branch of every line of code however. But some level of testing needs to be done.

Automated unit testing is your friend here.  With the advent of DevOps and Continuous Integration you will see more and more applications and methods of quickly and easily testing your code.  Of course that does require having a good test environment and data to go along with it, but that's a whole different discussion.


SQLite - No Such Column TABLE.ROWID I'm using this query on SQLite 3:

Select  Message.messageContent, Message.messageDateTime FROM Message INNER JOIN (Contact INNER JOIN contact_m2m_message ON Contact.contactID = contact_m2m_message.contactID) ON Message.msgID = contact_m2m_message.messageID;

it gave me this error: No Such Column: TABLE.ROWID

i'm confused as the very query was working fine when i tested over MS Access DB Engine just for the sake of confirmation.
.
is there something that i'm missing? or is there a difference in syntax in SQLite?
...
I also tried this:

Select  Message.messageContent, Message.messageDateTime FROM Message INNER JOIN (Select Contact.contactNumber, Contact.contactName FROM Contact INNER JOIN contact_m2m_message ON Contact.contactID = contact_m2m_message.contactID) ON Message.msgID = contact_m2m_message.messageID;

But this gives me the error:
no such column: contact_m2m_message.messageID ...  but it does exist in the definition.
.

.
The task i am striving to accomplish is to populate a list similar to Android Inbox view where each list item contains Contact Name, Number and a little Text from the latest message.

moreover, i'm testing queries using SQLite Studio v2.1.5

your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Contact INNER JOIN contact_m2m_message
            ON Contact.contactID = contact_m2m_message.contactID
        )
      ON Message.msgID = contact_m2m_message.messageID ;

Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.

The 2nd query is not valid because you have not provided an alias for the derived table:

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Select Contact.contactNumber, Contact.contactName
          FROM Contact INNER JOIN contact_m2m_message
               ON Contact.contactID = contact_m2m_message.contactID
        )     --<-------------------------------------- no alias
            ON Message.msgID = contact_m2m_message.messageID;

To make it  work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID in the SELECT list of the derived table (and remove columns that are not used):

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Select -- Contact.contactNumber, Contact.contactName,
                 contact_m2m_message.messageID
          FROM Contact INNER JOIN contact_m2m_message
               ON Contact.contactID = contact_m2m_message.contactID
        ) AS cm
            ON Message.msgID = cm.messageID;

But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:

SELECT
    m.messageContent, m.messageDateTime
FROM
    Message  AS m
  INNER JOIN
    contact_m2m_message  AS cm
      ON m.msgID = cm.messageID
  INNER JOIN
    Contact  AS c
      ON  c.contactID = cm.contactID ;


Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0? Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0?
The Compatible parameter need to be changed to "10.2.0"?
Today I have Compatible set to 10.2.0.4.0.

It's not clear on support.oracle if I need to change or not.
This document
http://www.oracle.com/technetwork/database/upgrade/upgrade11gr2-downgrade-methods-179029.pdf

state:

  The major restriction on the downgrade script is that it cannot be run
  if the COMPATIBLE parameter has been raised as part of, or after, the
  upgrade. This is because raising the COMPATIBLE parameter will allow
  on-disk changes that cannot be understood by the earlier version of
  Oracle Database. For this reason, downgrades from Oracle Database 11g
  Release 2 to Oracle Database 9i are not supported. Downgrades from
  Oracle Database 11g Release 2 to the following versions are supported:
   10.1.0.5  10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5  11.1.0.6,
  11.1.0.7

but this one
http://docs.oracle.com/cd/E11882_01/server.112/e17222/downgrade.htm#UPGRD12554

says:

  If you are downgrading to Oracle Database 10g Release 2 (10.2), then
  the COMPATIBLE initialization parameter must be set to 10.2.0 or
  lower.

thx

Yes, it is possible, as stated in the 11.1 documentation:

Supported Releases for Downgrading

Oracle mentions 10.2.0, because ideally you need not to set anything more specific (more digits). This is explained in:

How To Change The COMPATIBLE Parameter And What Is The Significance? (Doc ID 733987.1)

  Use only the first 3 digits for the compatible parameter unless there
  would be some very specific instructions to do otherwise.  Patch sets
  are normally not intended to add/change actual functionality, hence
  there is no need to increase the compatible parameter and set the 4th
  digit.

If you set it to 10.2.0.4.0, the downgrade should be still successful to version 10.2.0.4, but not anything lower.


Test Driven Design for postgres procedures I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation.

I have seen that pgTap is a popular unit testing tool for postgres and correctly allows for the tests to be written in SQL and not built and run externally.

In order to go the full distance I would also like to be able to use test doubles (stub, mock, fake, dummy) and maybe even some IDE support for running and refactoring (something like tsql)

Are there currently tools and resources available specifically for supporting TDD in postgres or do people roll their own by building fake tables and data and wrapping everything in transactions that roll back on completion?

What are currently the best practices for approaching TDD in postgres?

Test-Driven-Development (TDD) is an ideology. You can usually poke holes in implementations; and generally you sacrifice convenience and efficiency in any implementation of TDD -- which is why you have to sell it, and why it's not intuitive. You're running up against a problem which is one of the terminus ad quem of testing. You're likely trying to be a purist and often people get lost in the woods in that pursuit.

When you create a website, generally you don't test whether or not the framework, is producing a valid HTTP response. Such a test resides in a different layer, and you should only use frameworks and libraries that have a rigorous test suite that test their own domain. Anything you do to test a valid HTTP response should come up in end-to-end testing (with something like Protractor).

Think of PostgreSQL as a "framework" at a lower level that manages your data. You need to test how you get things in and get things out. Most of what PgTAP does is absolutely overkill. A ton of it is validating the schema -- how would that even work with TDD? Take..

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to test

foo exits and is a table
a is an interger
b is a text field
c is a uuid

All of that is well tested to be the result of,

CREATE TABLE foo ( a int PRIMARY KEY, b text, c uuid );

You're going to drive yourself nuts testing that. If you need to validate two schemas are the same, you can simply pg_dump --schema and diff the two. You can actually generate, with great ease, a full schema test suite that tests everything.

As far as what you need to test: any procedural code in the database. Generally we do this with test-data as PostgreSQL does, or by simply having a test-database which your framework can connect to and test the procedural code like everything else.

Shy of that, you want Fat Model, Skinny Controller (put as much as your data-manipulation logic in the model) so you can use it outside of your web app, and testing that should be done like anything else in whatever language you prefer.


Difference between DataLinks and External tables There was a feature called DataLinks that allowed to access data in files from SQL. This was introduced in Db2 V5.2 and removed in V9.7. However, in v11.5 there is a "new" feature called external tables.

What is the difference between these two approaches?

Data Links Manage - http://users.sdsc.edu/~jrowley/db2/Data%20Links%20Manager%20QB.pdf
External tables -  https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r_create_ext_table.html

If I remember correctly, Data Links Manager attempted to manage external files that were referenced in a database, rather than providing relational access to the data in those files. Think of an image or PDF document that would be related to a record. You could store those documents in the filesystem (instead of in a LOB inside the database). The files could be accessed outside of the database, but Db2 would also (supposedly) provide some control to prevent files from being deleted or moved.

It was, perhaps, closer in function to products like Content Manager OnDemand or Filenet.


Complex queries in CouchDB across multiple documents I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something.

Assume the following (simplified) document examples:

{ "docType": "school", "_id": "school1", "state": "CA" }
{ "docType": "teacher", "_id": "teacher1", "age": "40", "school": "school1" }

I want to find all the teachers aged 40 in California.

Maybe my mind is still stuck on SQL world, but I'm having a hard time understanding how to run a query that uses data from different documents...

PS. let me know if this is more suited to StackOverflow...

For quick one time queries I would use _find, for permanent ones where performance is important I would create a view for this.

Views are very well explained in the booklet "Writing and Querying MapReduce Views in CouchDB: Tools for Data Analysts".


Rule of thumb on what changes require a database reboot I am working on enabling SSL for database listeners. As a part of it, I have to modify the sqlnet.ora and listener.ora files. Does this require a database bouncing to take effect? Is there a general rule of thumb that I can follow when deciding whether or not to reboot the database for some changes to take place.

No reboot of the database is required vis a vis the listener.  The only communication from the db to the listener is for the database to register itself with the listener, which it does every several seconds, on its own. Changes to db init parms only require a reboot if the change was 'scope=pfile', and that is only necessary if the description of the parm (in the Database Reference) says 'modifiable - NO'.

The db and the listener are not as tightly linked as you seem to think (based on this and other questions you've asked).  They are separate and distinct processes, and the listener's only role is to receive connection requests, and set them up by spawning a dedicated server process or connecting the client to a dispatcher process.  Once that is done the listener is out of the picture and you can even stop it without impacting existing connections.


Can't deploy SSAS Tabular Model in Visual Studio I am following the SSAS Tabular Model Tutorial by Microsoft using the Adventure Works DW. I've imported the DW in SQL Server Management Studio to my local server. I've connected to the DW from Visual Studio, everything worked fine, completed all the steps of the tutorial until deployment. Building the Model works just fine, however when I try to deploy the Model it throws me the error

  Error Cannot deploy metadata. Reason: An error occurred while connecting to the server. 0

Some other users have also reported the same issue and could solve it with reinstalling SSDT, tried that, did not work for me.

Any ideas would be very much appreciated!

I just found out why I could not deploy my Tabular model to the server:

I tried to deploy it on the SQL Server (where my source DW is located), but obviously the Tabular Model needs to be deployed on the Analysis Server


multi-column FKs with NULL values I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the rows in B where the value of such columns is indeed NULL with no check for the presence of a corresponding row in table A (as primary key columns cannot be nullable):

SQL Fiddle

However, is this PostgreSQL-specific or is it in the SQL standard? (experimenting with SQL Fiddle it seems that both MS SQL Server and MySQL behave in the same way).

This behavior (allowing NULL values in FK columns) conforms to the SQL standard. The manual:

  The CREATE TABLE command conforms to the SQL standard, with exceptions listed below.

And there is no exception listed for FK constraints.

To disallow that some columns of a multi-column FK are NULL, use MATCH FULL instead of the default MATCH SIMPLE behavior. The manual:

  MATCH FULL will not allow one column of a multicolumn foreign key to
  be null unless all foreign key columns are null; if they are all null,
  the row is not required to have a match in the referenced table.

Or just define some or all column NOT NULL. More here (as @ypercube commented):

MATCH FULL vs MATCH SIMPLE in foreign key constraints


How to draw ER diagram for recursive relation when the number of roles is more than 3? I know for a recursive relation where there are 2 roles, for example:
Worker works for Manager
and Worker and Manager both are 2 roles of Employee, we show it this way:

But what if Employee had roles like:
1. Director
2. Manager
3. Secretary
4. Worker
and every role in the lower hierarchy works for the ones in the higher rank.

For Example:
Manager works for Director, Secretary works for Director and,   Worker Works for Director

Then again,
Secretary and Worker also Works for Manager and so on...

How to show that in the ER DIAGRAM?

Organisations are generally far more flexible than we initially think.
For example, Jon might have two jobs in the organisation (Developer and Acting Team Lead).

He may report to Bill the Development Manager for his Acting Team lead role and to Bob for the Development work that he still carries out.

This necessitates having the self referencing relationship for REPORTS_TO expand out into another entity type so that each person can report to many others rather than just one.
You also need to be able to show that each person can have many roles/jobs and the you may want to have some roles that officially report to multiple roles.

From another perspective hierarchies like this are worth exploring in a graph database as that is what they excel at. Not having a formal schema also means that they are very flexible.


How to implement an association with restrictions I'm working on an SQL database of useful plants. I'd like to be able to allow users to annotate the plants with a set of hierarchical categories and tags. For example:

category: medicine

body part: stomach
disease: IBS

An initial database structure could be:
Plant

id
name
category

1
ginger
medicine

PlantTags

id
plant_id
tag_id

1
1
1

2
1
2

Tags

id
type
value

1
body part
stomach

2
disease
IBS

I would like to restrict the categories and tags that can be used together. For example, one should only be able to apply the body part and disease tags if the medicine category is selected. Furthermore, one should only be able to select the disease: IBS tag if the body part: stomach tag is selected.
Put another way, I want a way to specify which combinations of tags are valid.
Is this possible at the database level?
EDIT:
Users should also be able to categorise a plant without tagging it. For example:
Plant

id
name
category

2
cedar
timber

EDIT 2:
Here is an example of a set of tags that should not be allowed:

id
type
value

1
body part
stomach

2
disease
pneumonia

pneumonia is a disease of the lungs, not the stomach, so having the two tags stomach and pneumonia should not be allowed.

There are a couple of ways you can go about this, but the most simplest way to enforce this would probably be to create a dimension combination table. Such a table is pre-filled with all valid possible combinations (by id) and then that is the source of your application's controls (such as dropdowns, etc) because as you filter on one dimension in that table, only valid entries for the remaining dimensions will be available.
For example, your dimension combinations table would have a plant_category_id column and a tag_type_id and maybe a second tag_type_id2 column (or if you normalized your Tags table, the column naming would be a little better in the dimensions combinations table).

The other thing you can do is implement a complex check constraint (possibly with a function) but this is probably more work than it's worth, and will depend on which implementation of MySQL and version you're using.

The third way is handling the logic in stored procedures that manage data access via the logic you want to implement, but probably would also be a lot of work, especially to maintain over time. So the first suggestion that makes it table driven would be my first pick.


SQL Server Enable filestream without having SQL Server Configuration Manager(so via command line only) I have SQL server 2017 on a Windows Server 2019 Core installation, no GUI.  One of our users wants to use Filestream.  I tried to follow this setup and this however, since it requires SSCM I can't complete that part.  I can enable in SSMS however, cannot set file location etc, so when executing the stored procedure command on the setup page I get this error:

  FILESTREAM feature could not be initialized. The operating system
  Administrator must enable FILESTREAM on the instance using
  Configuration Manager.

When I connect to SSCM remotely through the computer management window and try to open the services none will display.  I can access the networking options but not the services so maybe those won't work remotely?  The firewall is off between the Core install and the GUI version I am trying to use.

Anybody have a way to remotely enable Filestream and set the needed parameters like you would through SSCM?

Thanks in advance.

Regedit

You could change the registry settings for the instance's filestream settings.
Below is an example with the master..xp_regwrite procedure.

If filestream is disabled for the instance this shows:

The same is true when viewing with the configuration manager:

Via SSMS / SQLCMD / ...

As a result, you can change the EnableLevel value for the Filestream registry key. Remember to change MSSQL14.MSSQLSERVER in the example below to your sql server instance.

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
3

This changes the value to full filestream access:

This can be validated with the configuration manager:

As always, change the registry at your own risk. Taking a backup of the registry is also a good idea.

After changing these settings, you would have to restart your sql server instance and configure the filestream_access_level .

Extra info

Level 2:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
2

And as expected, level 1:

EXEC master..xp_regwrite
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Filestream',
'EnableLevel',
'REG_DWORD',
1


Converting colums as row My mysql data is like this

CREATE TABLE `extra_field` (
  `extra_field_id` int(11) NOT NULL,
  `data_item_id` int(11) DEFAULT '0',
  `field_name` varchar(255) DEFAULT NULL,
  `value` text,
  `import_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT '0',
  `data_item_type` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And the data is like this.

INSERT INTO `extra_field` (`extra_field_id`, `data_item_id`, `field_name`, `value`, `import_id`, `site_id`, `data_item_type`) VALUES
(1275, 1219, 'Education', 'MBA', 0, 1, 100),
(1276, 1219, 'Contract or Permanent', 'Permanent', 0, 1, 100),
(1277, 1219, 'Notice period', '30 days', 0, 1, 100),
(1278, 1219, 'Total Experience', '12 yrs', 0, 1, 100),
(1279, 1219, 'Relevant Experience', '12 yrs', 0, 1, 100);

I want to write a custom query which returns the value in this way.

id      Education   Contract or permanent   Notice Period   Total Experience    relevant experience
1275    MBA         Permanent               30 days         12 yrs              12 yrs

How can i do this? The id will never repeat. and the values will be having only one entry.

You need a pivoting function. MySQL doesn't have a pivot function per se, you can use conditional aggregation as a workaround.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id

Result:

data_item_id    Education   ContractOrPermanent Noticeperiod    TotalExperience RelevantExperience
1219            MBA         Permanent           30 days         12 yrs          12 yrs

The problem is that you will have to add as many columns and CASE with each different value that you have.

If you need this dynamically (variable amount of columns depending on row's data) you can use prepared statements but it's another level of complexity.


Configure Email to be sent on restoring a database Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.

That's suprisingly difficult.

SQL Agent Alerts were the first stop, but because a restore doesn't generate an "error message" there is no way to trigger it (I tried message levels 1 and 10, but no banana). It might be possible with a WMI query, but I'm not proficient in that.

Policies and extended events can create the necessary conditions but have no way to alert you.

Audits don't have the necessary conditions.

So, brute force then. The "restoring ..." message goes into the SQL Server log and into the Windows application event log with source MSSQLSERVER and event id 3402.

Working with that, you can attach a task to that event - unfortunately without having the option to filter based on the text which you need for the database name filter. So, configure the task to start a powershell script which then reads all the "MSSQLSERVER, 3402" events in , say, the last 2 minutes from the application log (hopefully you don't have mass restores all the time), checks for the correct db name and sends an email to you if it finds one with the wanted name.

As I said, brute force, but works.


The log for database 'tempdb' is not available 2017-07-06 05:53:02.69 spid17s     Error: 17065, Severity: 16, State: 1.
2017-07-06 05:53:02.69 spid17s     SQL Server Assertion: File: <latch.cpp>, line = 1538 Failed Assertion = '0' Attempted release of unacquired latch. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2017-07-06 05:53:02.69 spid17s     Using 'dbghelp.dll' version '4.0.5'
2017-07-06 05:53:02.70 spid17s     **Dump thread - spid = 17, PSS = 0x1589E7D8, EC = 0x1589E7E0
2017-07-06 05:53:02.70 spid17s     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump1824.txt
2017-07-06 05:53:02.70 spid17s     * *******************************************************************************
2017-07-06 05:53:02.70 spid17s     *
2017-07-06 05:53:02.70 spid17s     * BEGIN STACK DUMP:
2017-07-06 05:53:02.70 spid17s     *   07/06/17 05:53:02 spid 17
2017-07-06 05:53:02.70 spid17s     *
2017-07-06 05:53:02.70 spid17s     * Location:      latch.cpp:1538
2017-07-06 05:53:02.70 spid17s     * Expression:    0
2017-07-06 05:53:02.70 spid17s     * SPID:          17
2017-07-06 05:53:02.70 spid17s     * Process ID:    4572
2017-07-06 05:53:02.70 spid17s     * Description:   Attempted release of unacquired latch

The above is the error log details. It seems there is some issue with tempdb corruption. The instance restart fixed the issue and sql dump creation stopped.

What seems to have resolved is that tempdb gets recreated after the restart. What are your views on this?

That's likely a memory problem, or possible database corruption somehwere in your system. Run CHECKDB on all of your databases, and ensure that you do not have any hardware problems.
As for tempdb getting recreated, well yes, in a sense that is true, all objects are cleared, but the files themselves are not recreated (except in the circumstance that you manually delete the files from the OS). I Would recommend reading this post on the zeroing out of log files in tempdb.


Slow queries on SQL Server We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the archive. We have also another service which gets the clients information from archive as batches and calculate some another information for each row and rewrite them as batches again.

On the webhand-side we have 100-200 users online at a time and most of the queries depends on archive table. I built all possible indexes on archive and I'm using .NET Framework 3.5. I am connecting the database with standard connection string.

The problem is when a user request for one day long report it returns in 10-15 seconds for 50 rows. The one month long reports take more time like 2-3 min for 5k-6k rows. I am not a DBA but we don't have one so i am expected to tackle this problem. Can you make any suggestions for my problem?

Thanks.

As you didn't provide us with any detailed information, may I suggest you watching the following videos from Brent Ozar (Very smart MVP).

  http://www.toadworld.com/platforms/sql-server/w/wiki/10063.find-missing-indexes.aspx

  http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx

These videos will give you a basic idea of how indexing works and give you scripts to run against your database and check for missing indexes and indexes that are not used.

I hope these links can help you, as they did for me.


Restoring SQL Server 2012 database onto 2008 I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: http://learnsqlwithbru.com/2012/01/11/setting-database-compatibility-level-for-sql-server-2012/

However, the restore still doesn't work and I get "specified cast is invalid". Is there any way I can fix this problem?

Note: I don't want to use a Generate scripts option.

Compatibility level only controls what features may or may not be used on a given database and has no bearing on the restoration of DBs from different versions.

As pointed out by others, it can't be done by any means other than scripts or, if you can get it to work, the built in Copy Database tool. However that uses SSIS and I have never managed to get it to work successfully as it requires a whole heap of fiddly security apparatus, and usually consumes far more time than it's worth.

If you truly don't want to use scripting, then pretty much your only option is to upgrade your target DB server to 2012, or install a new one (using developer or Express editions is an option provided that a) It isn't a production instance if you use Developer Edition and b) the limitations of Express don't affect you).


Mirroring in SQL Server Standard Edition I have setup mirroring of a database in SQL Server Standard Edition.

After this I tried to see if the data has been synchronized or not.

I was not able to check data by taking a database snapshot since database snapshots are not available in SQL Server Standard Edition.

Is there any alternative solutions available to verify whether the data is synchronized or not?

Check the following system view:

SELECT *
FROM master.sys.database_mirroring;

There is a wealth of information in there about the status of your mirrored databases.

If the column mirroring_state_desc contains 'SYNCHRONIZED' then it is indeed synchronized.

What SYNCHRONIZED indicates is that all outstanding transactions from the master have been successfully committed into the mirror database.

If it shows a status of SYNCHRONIZING then the mirror is "behind" the principal to some degree.

You can also use the Database Mirroring Monitor app to monitor the status of mirrored databases.


Can i have one foreign key to both parent and it's childs in postgresql? I want to have a foreign key to reference table and it's children. Here's how I tried and failed:

CREATE TABLE parent (
 id serial,
 CONSTRAINT parent_pk PRIMARY KEY(id)
);

CREATE TABLE child (
  val text,
  constraint child_pk PRIMARY KEY(id)
) INHERITS(parent);

CREATE TABLE test (
  id serial,
  foreign_id bigint REFERENCES parent(id),
  constraint testpk PRIMARY KEY (id)
)

Lets say we have such data:

parent:
|id|
+--+
|1 | --public.parent oid
|2 | --public.child oid

child:
|id|val|
+--+---+
|2 |'a'| --public.child oid

what i whant to acheive is this:

test:
|id|foreign_id|
+--+----------+
|1 | 1        | --(from public.parent table)
|2 | 2        | --(from public.child table)

is there a good workaround here?

this is not possible out of the box. inheritance in PostgreSQL is based on individual tables, which are only logically connected through pg_inherit. Each table is still somewhat independent. Therefore you cannot create a key on the entire structure. It is only possible to reference individual tables. What you can do, however, is to write a trigger ensuring integrity. A foreign key is basically the same behind the scenes anyway.


Performance of updating row if content has changed I'm trying to make a website where there is a form that contains input type fields that are editable. In fact I get the elements from one of my tables (containing an id column and a string column) from my database, which I display via input with id as the id in this database and string as the string in this database. The user can edit the text in these fields and also there is the possibility to add a new 'row' (input with string, but which refers to no row in the database).

Finally, there is a button to save.

My question is do I directly modify the existing lines even if the user has not changed the string or should I check if the string has been updated? Which method is best in terms of performance?

If your business logic layer(s) know that there has been no change (i.e. it has already re-read the data and can compare it to the provided input then you can simply not send anything to the DB.

If you don't know before hitting the data layer if there has been any change or not then you can do something like:

UPDATE yourTable
SET    field1 = @input1
  ,    field2 = @input2
       ...   ...   ...
  ,    fieldN = @inputN
WHERE  idField = @ID
AND    (
           field1 <> @field1
        OR field2 <> @field2
           ...   ...   ...
        OR fieldN <> @fieldN
       )

This avoids any update if nothing has changed so it more efficient (the lookups needed to reject the update would be needed to perform it anyway and the extra CPU time for the comparison is vanishingly small unless you have huge data in any of the columns), but it is more work to maintain and more prone to errors so most of the time I would recommend keeping it simple and updating without checking every column, i.e. the standard:

UPDATE yourTable
SET    field1 = @input1
  ,    field2 = @input2
       ...   ...   ...
  ,    fieldN = @inputN
WHERE  idField = @ID
-- note no comparison, just update

The maintainability issue is even worse for NULLable columns as fieldN <> @fieldN has to be changed to (`fieldN <> @fieldN OR (fieldN IS NULL AND @fieldN IS NOT NULL) OR (fieldN IS NOT NULL AND @fieldN IS NULL) because comparisons between NULLs are always false (NULL is not equal to NULL, but it is also not not equal to NULL - NULL by definition is unknown so all comparisons (=, !=, <, >, ...) return false unless your DB supports non-ansi behaviour like https://stackoverflow.com/questions/9766717/). If this is all baked into you data access layer then fine, some ORMs for instance provide such optimisation as an option, but you don't want to be manually writing and maintaining it for every update statement in your application.

The DB (ever engine I'm aware of) will perform a full write of the relevant data pages even if no values have changed - they won't spend the few extra CPU cycles checking. Away from the efficiency of the update itself you need to consider knock-on effects and certain business requirements. If there are triggers that take action on updates to the table they will fire even if nothing has changed, this could be a significant consideration if you have any complex triggers to consider. From a business logic point of view do you need to log that the user actively saved the record with no changes? If you are relying on automated change capture of some sort (such as your own triggers, temporal table features in postgres and the upcoming MSSQL2016) to track changes then you (or your end users) need to decide if the data model should care that a no-change update happened or not. Of course if the record includes a "last saved" column this all becomes a moot point - there is always something to update upon save if only that one column.


Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem? Background:

Oracle 11g running on Windows 2003 x64 SP2 on a 3.06GHz Xeon X5675 with 16GB RAM

Over the past 3 weeks, a query I used to run approximately every 5 minutes has started to respond very very poorly - moving from an execution time of around 50 seconds to sometimes over 11 minutes. The data has hardly changed and returns approximately 69000 records  If I restart the service, I find the query runs very quickly the first time but subsequent runs take an age to run and lock the CPU on the server at nearly 100%. This server is used for test/development purposes so is rarely under any serious load.

This ONLY happens when I run the query from a remote machine - running the query in SQL Plus on the server and dumping the result to a file gives a 25MB text file in about 1 second which made me think I have a network problem....

When I stop the service and use iperf.exe to transfer 25MB across the network on the same port of 1521, it takes 50 seconds no matter how many times I run it. It's not bandwidth related - Oracle just seems to choke.

I'll include the SQL statement but it is important to know that other similar queries which join the same tables are struggling to run in a timely fashion and yet run very quickly when ran locally. Again, those queries suffer from serious slow-down after their first run.

If anyone can give me any pointers on where to look for potential issues, I'd be eternally grateful!

SELECT
    (CASE
        WHEN t10.COURSE_ID IS NULL THEN t3.COURSENAME
        ELSE t10.COURSENAME
     END) AS C1, t3.LANGUAGE_CODE AS "LanguageCode"
FROM MYSERVERNAME.T_COURSETYPE t1
INNER JOIN MYSERVERNAME.T_CURRENCY t2 ON t1.CURRENCY_ID = t2.CURRENCY_ID
INNER JOIN MYSERVERNAME.T_COURSENAME_LANG t3 ON t1.COURSECODE_ID = t3.COURSECODE_ID
INNER JOIN MYSERVERNAME.T_COURSE t4 ON t1.COURSETYPE_ID = t4.COURSETYPE_ID
INNER JOIN MYSERVERNAME.T_PROFITCENTER t5 ON t4.PROFITCENTER_ID = t5.PROFITCENTER_ID
INNER JOIN MYSERVERNAME.T_COUNTRY t6 ON t5.COUNTRY_ID = t6.COUNTRY_ID
INNER JOIN MYSERVERNAME.T_COMPANYUNIT t7 ON t5.COMPANYUNIT_ID = t7.COMPANYUNIT_ID
INNER JOIN MYSERVERNAME.T_VENDOR t8 ON t7.VENDOR_ID = t8.VENDOR_ID
INNER JOIN MYSERVERNAME.T_COURSELOCATION t9 ON t4.COURSELOCATION_ID = t9.COURSELOCATION_ID
LEFT OUTER JOIN MYSERVERNAME.T_COURSENAME t10 ON (t4.COURSE_ID = t10.COURSE_ID) AND ((t3.LANGUAGE_CODE = t10.LANGUAGE_CODE) OR
((t3.LANGUAGE_CODE IS NULL) AND (t10.LANGUAGE_CODE IS NULL)))
LEFT OUTER JOIN MYSERVERNAME.T_ESHOP_COUNTRY t11 ON t6.COUNTRY_ID = t11.COUNTRY_ID
WHERE (t4.FROMDATE > DATE '2014-06-17') AND (t4.HIDE_ONLINEBOOKING = 'N') AND (t1.ACTIVE = 'A') AND ((t1.TMS IS NULL) OR (t1.TMS = 'N'))
AND (t8.EXTERNAL_VENDOR = 'N') AND (t1.VENDOR_ID <> 66) AND (t4.TYPEOFFEE = 'D') AND (t9.HIDE_ONLINEBOOKING IS NOT NULL) AND
(t9.HIDE_ONLINEBOOKING = 'N') AND (((t11.ACTIVE IS NULL) AND (t6.ONLINEBOOKING = 'Y')) OR (t11.ACTIVE = 'Y'));

You really need to generate an explain plan. Since your query has an OR there may be situations where the query is being short circuited based on data not existing, hence the performance is not consistent. Also, don't join everything together, subqueries with low cardinality put at the top in a WITH subquery_name clause, and join similar tables together, then join them with other tables.


In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure) We had in our WordPress/Woocommerce table (hence mySQL) ab_postmeta the sale (discount) prices for some products.

We thought that reseting the sale prices via SQL query (and not via WordPress because they were too many) would be ok. What we did was:

Update ab_postmeta set meta_value='' where meta_key='sale_price'

For some parts of WordPress it worked for other ones it did not. At a closer inspection we saw that the ab_postmeta table had also other values which should be reset to the normal price in order to work. Our main difficulty is that the table has a vertical key=value layout with the following structure:

ab_postmeta

meta_id - PK
post_id - FK to the post/product
meta_key - key
meta_value - value of the key (property) above

Basically, we need an Update query which will take the (numeric) value of the meta_key='_max_variation_regular_price' and put it in meta_key='_price' and in meta_key='_max_variation_sale_price' and in other key values for the same post_id. It is ok if we run the SQL query multiple times for each target key from console.

How to do that? (I'm afraid to experiment, because I'm on a live db).

UPDATE:

Here is a sample of data: (what we need to do is: all „263”s must become „329”s - We must copy from one source record in another destination record based on Post_id (must be the same) and meta_key - must have the corresponding values):

UPDATE 2: In fact we need a translation in SQL of the following: "For each post_id take the value from meta_value where meta_key='_max_variation_regular_price' and put it in meta_value where (for example) meta_key='_price' (We will change your example for each destination row we need)

My translation of your problem is (slightly altered from your description):

For each post_id, find the value from meta_value column where meta_key = '_max_variation_regular_price' and put it in meta_value in all other rows with same post_id and where the meta key is a "price" (i.e. meta_key LIKE '%\_price')*.

Now, before we do anything and change the data in a production database, the best approach is to take a backup, restore the backup as another database and play around in the restored (copy) database, where we won't mind making mistakes.

Lets identify the rows that have the values we need:

-- query1
SELECT post_id, meta_value AS price
FROM ab_postmeta
WHERE meta_key = '_max_variation_regular_price' ;

If (post_id, meta_key) has a UNIQUE constraint, that's all the values we need. If not, we may have to take an additional step to make sure that the result has one value for every post_id and not more (skipping this step).

Now, lets find the rows that need updating:

-- query2
SELECT post_id, meta_key, meta_value
FROM ab_postmeta
WHERE meta_key LIKE '%\_price'
  AND meta_key <> '_max_variation_regular_price' ;

Then we can combine the two selects into one:

-- query3
SELECT upd.post_id, upd.meta_key,
       upd.meta_value  AS old_price,
       good.meta_value AS good_price
FROM ab_postmeta AS good             -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
WHERE good.meta_key = '_max_variation_regular_price'
  AND upd.meta_key LIKE '%\_price'
  AND upd.meta_key <> '_max_variation_regular_price' ;

We can run this and inspect the results to see what an update will. No UPDATE run yet!. After that - and if the inspection seems right - we can run the actual UPDATE. Note that it's very similar to the previous SELECT. The WHERE clauses are identical and the FROM clause has become UPDATE. But we'll do it inside a transaction, just to be sure:

BEGIN ;           -- start a transaction.

UPDATE ab_postmeta AS good           -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
SET
    upd.meta_value = good.meta_value
WHERE good.meta_key = '_max_variation_regular_price'
  AND upd.meta_key LIKE '%\_price'
  AND upd.meta_key <> '_max_variation_regular_price' ;

Lets run query2 again, to check the new values:

SELECT post_id, meta_key, meta_value
FROM ab_postmeta
WHERE meta_key LIKE '%\_price'
  AND meta_key <> '_max_variation_regular_price' ;

Now, is everything OK? Do all the updates look good?

If no, run ROLLBACK ; and no harm was done. Start again, by evaluating your algorithm or running with a smaller set.
If yes, run COMMIT ; and the changes will be permanent. You can safely do this (again) in production.

* Thnx to Andriy for the bugfix. the LIKE should escape the _ character with \_ as it's a wildcard.


SSAS Calculated measure with SCOPE. I'm building an SSAS OLAP cube on Meteorological data. Some of this data can be totaled (eg rainfall) and some can't (like wind direction).

I want totals, but only on data sources where it makes sense. (Where the interpolation method = 3.)

I've tried adding SCOPE with this:

SCOPE ([Measures].[Total]);
SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]);
END SCOPE;

And get warnings:

  Invalid query syntax. A valid MDX or DAX query was expected

and:

  One or more errors were encountered in the MDX script

What am i missing?

I don't have your SSAS database so I can't test the syntax, but I believe you would need to write your MDX as follows (notice the THIS= addition):

SCOPE ([Measures].[Total]);
THIS = SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]);
END SCOPE;

See the documentation for syntax details.