oracle 10g xe to 11g xe upgrade The oracle 10g DB that is being currently used has exceeded the limit of 4GB. For the application to continue the db needs to be upgraded to 11g. But the upgrade doc requires a data pump export to be done from 10g and then data pump import it to 11g.

But the concern is the 10g exceeding its limit of 4GB and hence will not allow a data pump export to be done. Is there any alternate option to move the data to 11g other than data pump export and import.

Have tried the export with expdp but i get an error saying the database has exceeded th 4gb limit.

I would try the following:

Install an oracle 10g database  software that I download from the oracle software download page (i have the appropriate license)
create an empty database with this software
using the exp-tool (classical export, not datapump) to make an export from the database
import the dumpfile (with the classical imp tool) in the empty 10g database I created
now i do the export from this database and import this in the 11g database

step 3. is possible because the classical export does not create any table in the database in contrast to the datapump export. so i think i will not hit the 4g LIMIT ERROR

step 5 is possible because now the expdp is not constrained by any 4G limit.

it is not supported to import in step 4 into an 11g database but one can try if this is possible.


Invalid column name 'database_id' in sp_blitzfirst I am receiving the following in messages when running the sp_blitzfirst @expertmode = 1 on the following box:

kit version: SQL-Server-First-Responder-Kit-20161210

Microsoft SQL Server Standard Edition (64-bit) version 10.50.6000

  Setting up configuration variables
  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.
  Now starting diagnostic analysis
  Capturing first pass of wait stats, perfmon counters, file stats
  Beginning investigatory queries
  Finished running investigatory queries
  Waiting to match @Seconds parameter
  Capturing second pass of wait stats, perfmon counters, file stats
  Analyzing changes between first and second passes of DMVs
  Analysis finished, outputting results
  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.

Here is what I get running sp_blitzwho:

  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.

Any help would be appreciated.

First, thanks for using our scripts.

Second, sorry you ran into this problem.

For support, the best place to file issues is here. Just remember to search for issues first before filing.

There's a fix for this in this commit. You can grab the version from there to use until this month's release drops.

Thanks!


Convert two integers into timestamp How do I convert two integers, one for date and one for time, into a timestamp?

Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
Given 20200304 and 946, return a timestamp for April 3, 2020, 9:46 AM.
Given 20200304 and 2359, return a timestamp for April 3, 2020, 11:59 PM.
Given 20200304 and 0, return a timestamp for April 3, 2020, 0:00 AM.

I'm running Version 7, Revision 1.


Migrating data from a boolean column to a varchar column My table currently has a boolean column, let's call it the "important" column, reflecting whether a record is urgent or not. I want to transition to using a varchar column that reflects a record's urgency as one of a few possible values, like "normal", "urgent", "extreme". The column name needs to change to, so I'm planning on doing something like this: create a new column called "urgency" to hold the normal/urgent/extreme value, and selectively migrate the existing boolean date into the new column.

Is this a good approach, and are there any gotchas I should watch out for?

In MySQL, the BOOLEAN data type does not really exist. If you create something like this:

mysql> CREATE TABLE imp (id int PRIMARY KEY auto_increment, important BOOLEAN);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO imp (important) VALUES (0), (0), (0), (1), (0), (1), (1), (1), (0), (0), (0), (1);
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

You will see that BOOLEAN is just an alias for tinyint(1):

mysql> SHOW CREATE TABLE imp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| imp   | CREATE TABLE `imp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `important` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

And you can even insert a 2 value:

mysql> INSERT INTO imp (important) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM imp WHERE important = 2;
+----+-----------+
| id | important |
+----+-----------+
| 13 |         2 |
+----+-----------+
1 row in set (0.00 sec)

The less traumatic way, but more secure and flexible that I can think is, instead of creating an enum or a string, reuse the column and set a foreign key if you are using InnoDB:

mysql> CREATE TABLE levels (id tinyint primary key, name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into levels values (0, 'normal'), (1, 'urgent'), (2, 'extreme');  Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE imp ADD CONSTRAINT important FOREIGN KEY (important) REFERENCES levels(id);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

That way you cannot insert values that do not exist on the levels table and you can add values later very easily (also, you do not have to rebuild the original table).


What does node eviction mean in the Oracle RAC world? Software environment: Oracle 12c with RAC running on RHEL

The question might sound rather banal, but I wasn't able to find any word on this in the Oracle documentation I read thus far.

By searching the net, I came to the conclusion that node evictions usually imply a node reboot, but that doesn't always seem to be the case, some suggest node evictions without node reboots are also possible.
If that is indeed the case, when will the node be rebooted and when not? If a node cannot access the minimum required number of voting disks, then that node will be evicted. Will that node always be rebooted because of this eviction? Can one configure this behavior (i.e. change from eviction with reboot to eviction without reboot)?

What is node eviction?

  Word Meaning(Merriam Webster Disctionary): to force(someone) to leave
  the place.

The process of removing the failed(due to various reasons) node from the cluster is known as eviction. Prior to 11gR2 Oracle tries to prevent from split brain situation by quickly rebooting the failed node . After 11gr2 Clusterware will attempt to clean up the failed resources . If the clusterware is able to clean up the failed resources, OHASD will try to restart the CRS stack. Once this task is done all the cluster resources on that node will be started automatically. This is called reboot less fencing(or eviction).
If clusterware can not stop or clean the failed resources then it will roboot the node.

Causes of node eviction

-Missing network heartbeat
-Missing disk heartbeat
-CPU starvation issues
-Hanging cluster processes
-May have more...

  Will that node always be rebooted?

Same applies as above mentioned.

  Can one configure this behavior?

No, Oracle Clusterware will decide on it.

If you want to learn more on it you can google the term 'Rebootless Node Fencing or Eviction' I promise you will have number of options to carry on.


Postgresql Total elements and affiliation users I have a two table that contains varchar:
items_tags_sets: item_id, user_id, tag
and
users: user_id, name.
How to make a query that displays a table with columns:

Certain Item
Tags belonging to a specific element.
Total Number of duplicate tags.
The fact that a particular user has set this tag, like other users.

I need a result like this:
----------------------------------------------
| item_id |    tag    | tags_count | its_tag |
|--------------------------------------------|
|   it1   | tag_test1 |      3     |  usr1   |
|   it1   | tag_test3 |      2     |         |
|   it1   | tag_test5 |      1     |         |
----------------------------------------------

Link to code: https://rextester.com/live/KXLJX41855

You need to remove user_id from your group by clause. But that requires putting it into an aggregate function in the SELECT list. One way to do that is to use string_agg()
SELECT its.item_id,
       its.tag,
       count(its.tag) as tags_count,
       string_agg(u.user_id, ',') as user_ids
FROM items_tags_sets its
  LEFT JOIN users u on u.user_id = its.user_id and u.user_id = 'usr1'
WHERE its.item_id = 'it1'
GROUP BY its.item_id, its.tag
order by its.item_id, its.tag;

Online example


Solve conflict between affinity mask and I/O affinity mask? I executed this on one of our SQL Server machines:

EXEC sp_configure 'show advanced options', 1

And then executed RECONFIGURE. This presented me with the following error message:

  The affinity mask specified conflicts with the IO affinity mask
  specified. Use the override option to force this configuration.

By executing RECONFIGURE WITH OVERRIDE I can indeed force the configuration. But this is not a solution to the conflict mentioned in the error message.

The values in sp_configure are as follows:

name                 minimum        maximum       config_value   run_value
affinity I/O mask    -2147483648    2147483647    126            126
affinity mask        -2147483648    2147483647    126            126

I found some explanation here, but this basically just says "There should not be a conflict", nothing on how to solve it. Could someone knowledgeable please explain to me what this error message actually means and how/if I should try to solve it?

Update 12/12/2012

We couldn't find any reason for the setting of conflicting values for affinity I/O mask and affinity mask. I changed them back to the default values, 0 for both. After the reconfiguration affinity mask is set to 0 immediately, affinity I/O mask apparently requires a restart of the instance.

The 'affinity mask' and 'affinity I/O mask' configuration values of
  SQL Server should not conflict. Performance may suffer if you choose
  to affinitize a processor for both SQL Server worker thread scheduling
  and for I/O processing.

What is meant by the above statement is:

  DO NOT CONFIGURE SUCH THAT A PROCESSOR IS SET IN BOTH WORKER AND
  I/O MASKS!

It's stated in the documentation and is protected from a mistyped attempt by the error message and requirement to specify WITH OVERRIDE to force the change through. That was your warning that something bad could happen.

  Msg 5834, Level 16, State 1, Line 1 The affinity mask specified
  conflicts with the IO affinity mask specified. Use the override option
  to force this configuration.

IO affinity masking is a rarely used optimisation, which offloads IO completion activity to a dedicated CPU. When set, a hidden scheduler is created that handles only IO operations for that instance. If you configure identical CPU and IO masks, the two schedulers will battle it out for CPU cycles and cause chaos.

The masks you've set are creating exactly that scenario with 1111110 specified for both.

If your intention is the more common requirement to dedicate CPUs to particular instances on a shared server, use just the CPU affinity mask and leave the IO mask as 0 (disabled).

I've encountered a mis-configured server with matching masks only once. I was put in front of it because the logs suddenly started filling up with the message we all dread to see:

  I/O requests taking longer than 15 seconds to complete on file...

After resetting the IO mask and restarting, the server behaved perfectly and the error above was never seen again.

Useful references:

Books Online - affinity I/O mask Server Configuration Option.
CSS - How it works: IO Affinity Mask - Should I Use It?.


select where column with a % How can I select all records with a % in? I think this code doesn't work.

SELECT column1, column2 FROM table WHERE column1 LIKE '%%%'

first and last % for zero, one or more chars
second % for the percent skinn

Can anyone suggest me how it can work? I use MS-SQL server.

Thanks in advance

For special characters (% and _), you need to escape them:

WHERE column LIKE '%[%]%';

or

WHERE column LIKE '%\%%' ESCAPE '\';

This is covered in the documentation for LIKE, in the sections "Using Wildcard Characters as Literals" and "Pattern Matching with the ESCAPE Clause." Your first stop should always be the official documentation.


How much work actually happens on SSMS and how much on the server itself? Suppose I run the following query:

declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END

Is the loop actually running on the client machine, and calling insert each time over the network, or is the loop running on the server and making only local calls?

On the same lines, is it better to run such queries by connecting to the server using SSMS, or by using RDP to connect to the server and using the local SSMS instance?

The SQL Server Management Studio only sends the query (or T-SQL prodcedure) to the server and waits for the result.

There's a difference between running it from LAN or directly on the server ONLY because results has to be transferred on your PC (so the difference is only the time to transfer data on LAN).
(Also, in the first case, your PC has to be turned on for the entire duration of the query.)


SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider' Inside of our SQL Server, we have created a linked server to an Oracle server.  We ave mapped several users to the Oracle server and specified the credentials.  On the Linked Server 'Security' properties, it says, "For a login not defined in the list above, connections will:" and we have selected "Be made using this security context:" and specified an Oracle user with read-only access.

Now, when we open SQL Server Management Studio as a user that is not explicitly specified in the list defined for the Oracle linked server, it uses the credentials with read-only access to connect.  However, when we try to expand 'Catalogs' under the linked Oracle server, we receive the error:

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'

On the SQL Server where the linked server is defined, execute:

Use MASTER
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO public

Does anyone have any comments on the security implications of this?


The problem is that although the user was not listed in mysql.user table, there was an entry in mysql.db with that user.

So the solution is: check also mysql.db for colliding usernames.


Search, filter and group across all columns in a tall and wide dataset quickly We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows.

For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users to pick the fields they want, and perform filtering on any of these fields. In addition, they should also be able to group by any number of these fields (nested groups), sort by any of these fields, and perform aggregations across some of the grouped fields (sum, count, avg, min, dates).

Our source data is spread across several tables in SQL Server and while the above is possible by dynamically generating the SQL in code, it is slow. Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow; specifically grouping by multiple columns is slow, and adding another column to group by can add a few seconds to the query time.

Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.

In what way can I accomplish this?

To be specific, we will be building an interactive grid in AngularJS which calls out to an API with the fields the user wants to query, group, filter, sort on. The API is in C# and its current backend is SQL Server, but we can look at something else if needed. It would be nice to have the query results return in less than 30 seconds.  Because it is web based, there will be concurrent users.

Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow [...] Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.

From a SQL Server point of view, the most obviously applicable technology solution is to create a clustered or nonclustered columnstore index (on all columns) on the 'precomputed' table.

Nonclustered columnstore indexes are available from SQL Server 2012 onward, though the table is then read-only. Given the 'precomputed' word, I assume this would be acceptable. Updatable and clustered columnstore indexes were added in SQL Server 2014. Both require Enterprise Edition.

Assuming your table data types are compatible, and enough memory is available, a columnstore solution ought to provide you with very fast results for the unpredictable select, project, and group by queries you describe.

For further details, see the excellent SQL Server Columnstore Index FAQ and Columnstore Indexes Described on MSDN.


Attempting to backup to remote NAS but access is denied I was attempting to use Ola Hallengren's scripts to backup to a remote NAS, but it was failing and not giving any direct errors, so I tried doing it manually using Server Management Studio and got the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The operating system returned the error '0x80070005(Access is
denied.)' while attempting 'SvlPathDoesPathExist' on
'\Dl-nas-01\LOCAL FILE BACKUP\OLASQL'. The statement has been
terminated. (Microsoft SQL Server, Error: 3634)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2002&EvtSrc=MSSQLServer&EvtID=3634&LinkId=20476

Now, the account I am signed into has FULL permission and access to that share, my connection is my domain\myaccount, and from the VM I can even put \\Dl-nas-01\LOCAL FILE BACKUP\OLASQL into a folder browser and access it.
Does SQL Server run these from a different account I don't know about? Backing up to a local location works fine, what's the solution? I'm pretty lost.

You mentioned this is a NAS, but this doesn't look like a valid network path:

\Dl-nas-01\LOCAL FILE BACKUP\OLASQL

Try using two slashes at the beginning, like this:

\\Dl-nas-01\LOCAL FILE BACKUP\OLASQL


Aborted connection on an Update statement only I have node-mysql client that gets "ER_NO_SUCH_USER" whenever it does an UPDATE query. All other type of query are successful but UPDATE fail.

Meanwhile in the server's log I get the standard:
"[Warning] Aborted connection ... (Got an error reading communication packets)"

The client's DB user has the following permissions:
GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON . TO ''@'%' IDENTIFIED BY PASSWORD ''

I sort of found the solution:
I tried running the same query directly on a mysql client, and the error was more enlightening: "MySQL error 1449: The user specified as a definer does not exist".

Which led me to this post:
https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist

I did apply the proposed solution to "Change the definer". However I was never able to make it work, as the client kept trying to reach the initial definer user (which no longer existed). It seems the user credentials were cached somewhere. So my only solution was to recreate the initial definer. Which stopped the error


Is it possible to select distinct values in a json document using PostgreSQL? I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents

{
  id: 1,
  s: "foo"
},
{
  id:2,
  s: "bar"
},
{
  id:3,
  s: "foo"
},

the query must check the "s" key for distinct values and return the documents with id 1 and 2.

Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job:

SELECT DISTINCT ON (doc->'s') doc
FROM  (
   SELECT '[
    {
      "id":1,
      "s":"foo"
    },
    {
      "id":2,
      "s":"bar"
    },
    {
      "id":3,
      "s":"foo"
    }]'::jsonb AS j
   ) t
   , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER  BY doc->'s', rn;

Or, unless s is a nested object, it's probably cheaper to fold on the text value instead of the jsonb (sub-)record. Just use the operator  ->> instead of -> in this case. The result is the same:

 doc
----------------------
'{"s": "bar", "id": 2}'
'{"s": "foo", "id": 1}'

Replace the subquery t with your actual table.

Key elements are jsonb_array_elements() (or json_array_elements()) in a LATERAL join with WITH ORDINALITY and then the Postgres-specific DISTINCT ON.

Related, with more explanation:

How to remove object from json array?
How to turn JSON array into Postgres array?
How to preserve the original order of elements in an unnested array?
Select first row in each GROUP BY group?


SELECT that is null if there is one more register in this date that is not null I'm trying to make a query that I can't produce...

This is my table capturainfo

captureid   int(11)
userid      int(11)
date        date
dateinit    datetime
datefin     datetime

I need to select all rows that have datefin=null and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2.

I just can't do it.

Here are some attempts I've made:

The basic one:

SELECT * FROM capturainfo where date < "2015-02-27"
AND datefin is null

it gets me all datefin is null, but I can't know if there is one more row in the same day.

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )

... Well this last one doesn't work.

here is the data sample

+-----------+--------+------------+---------------------+---------------------+
|     id    | userid |    date    |      dateinit       |     datefin         |
+-----------+--------+------------+---------------------+---------------------+
|    0001   | 2034   | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
|    0002   | 1789   | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
|    0003   | 1432   | 2015-02-20 | 2015-02-20 12:55:33 |         NULL        |
|    0004   | 1432   | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
|    0005   | 1444   | 2015-02-20 | 2015-02-20 14:41:31 |         NULL        |
+-----------+--------+------------+---------------------+---------------------+

In this case, query should return id 0003 because user 1432 have made a posterior register in the same day

Anybody knows how to do it?

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not smaller or equal (<=). You also erroneously compare datefin columns in the subquery when you should compare dateinit columns. It seems you want to get rows from dates older than 2015-02-27:

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.date = b.date AND a.dateinit < b.dateinit AND a.userid = b.userid )


How to avoid multiple SELECTs from a bag of properties table?￼ I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is required. You simply add a record to the bag of properties table instead.

Now, I need to retrieve multiple properties inside a function (or stored procedure). But since the results are returned as multiple rows, with this approach I cannot do a simple:

SELECT prop1, prop2 FROM settings;

The query returns multiple rows so the results cannot be saved in local variables with the SELECT col INTO var statement either. Yes, I can run two or more consecutive SELECTs but this doesn’t seem like a particularly efficient and elegant solution:

SELECT value FROM settings WHERE key = ‘prop1’;
SELECT value FROM settings WHERE key = ‘prop2’;

A couple of ideas I have considered:

FOR..IN loop

The function has OUT parameters, in this case called prop1 (VARCHAR) and prop2 (BOOLEAN):

DECLARE
  settings_row RECORD;
BEGIN
  FOR settings_row IN
    SELECT key, value FROM settings
  LOOP
    CASE
      WHEN settings_row.key = 'prop1' THEN
        prop1 := settings_row.value::VARCHAR;
      WHEN settings_row.key = 'prop2' THEN
        prop2 := settings_row.value::BOOLEAN;
      -- more cases handled here in case you needed other properties
    END CASE;
  END LOOP;

  RETURN;
END;

Cross join

Cross joining the settings table with itself, forcing the two properties to end up in columns. You could perhaps live with that if you needed just two properties but with any other value requiring yet another cross join this doesn't seem like a particularly scalable solution.

SELECT
  s1.value AS prop1, s2.value AS prop2
FROM settings s1
CROSS JOIN settings s2
WHERE
  s1.key = 'prop1' and s2.key = 'prop2';

Why not return the properties as key/value pairs wrapped in a single JSON value?

select jsonb_object_agg("key", value)
from settings
where "key" in ('prop1','prop2');

That can easily be wrapped into a function:

create or replace function get_properties(VARIADIC p_keys text[])
  returns jsonb
as
$$
  select jsonb_object_agg("key", value)
  from settings s
  where "key" = any(p_keys);
$$
language sql;

Then you can use

select get_properties('prop1','prop2');

Or if you do need it as columns:

select t.props ->> 'prop1' as prop1,
       t.props ->> 'prop2' as prop2
from get_properties('prop1','prop2') as t(props);


NoSQL- automatically cache and update references to other documents as embedded To avoid having to look up referenced documents, one practice is to embed them (e.g. https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/).

Is there a way to have those embedded documents actually be separate and be referenced yet be "automatically embedded" like by some sort of functionality which automatically stores their data in the document which is referencing them - almost like an automatic "embedding cache" of sorts.

I'm not referring to them being automatically retrieved by reference, but an actual copy being stored and updated automatically.

I know this could be built manually, but I'm wondering what may exist already which can do this, preferably something inherent to MongoDB, but it doesn't have to be.

While I'm asking this about MongoDB in particular, if someone knows of something like this for other NoSQL databases, I'd be interested to hear about that as well as an answer.

You may be looking for MongoDB Aggregation pipeline stage $lookup.

Example:
A collection orders contains the following documents:

{ "_id" : 1, "item" : "abc", "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1 }

Another collection inventory contains the following documents:

{ "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
{ "_id" : 4, "sku" : "jkl", description: "product 4", "instock" : 70 }

The following aggregation operation on the orders collection joins the documents from orders with the documents from the inventory collection

db.orders.aggregate([
    {
      $lookup:
        {
          from: "inventory",
          localField: "item",
          foreignField: "sku",
          as: "inventory_docs"
        }
   }
])

Example output:

{
  "_id" : 1,
   "item" : "abc",
  "price" : 12,
  "quantity" : 2,
  "inventory_docs" : [
    { "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
  ]
}

See more examples.

You could combine $lookup with $out stage to create another collection. See related $graphLookup


Create an unique integer id column for an existing string column I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this:

| ID_STRING |
| 'ABCDEFG' |
| 'HIJKLMN' |

Now I want to compute integer ids for each element and store them into an additional column. The result should look like this:

| ID_STRING | ID_INT
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2

Is there an easy way to achieve this?

To add the new column use:

ALTER TABLE the_table ADD COLUMN id_int integer;

To populate the new column you need an UPDATE statement.

I am assuming you have a primary key column named pk_column in your table. Obviously you need to replace that with the actual primary key column in your table.

update the_table
   set id_int = t.rn
from (
  select pk_column,
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.pk_column = t.pk_column;

If you really have a table without a primary key (why?), you can use the built-in ctid instead:

update the_table
   set id_int = t.rn
from (
  select ctid as id_,
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.ctid = t.id_;


How to show number of tables for each database in Postgres? Related to my previous question, I use the code below to get all databases:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';

How to get the number of tables from each database returned from the command above?
There is no master database in Postgres like Microsoft SQL Server, where I could do a query for all the databases.
Is it possible in Postgres?
Thank you

You can use something like the following which will list every table (with their row counts) for every schema in the database:
select n.nspname as table_schema,
       c.relname as table_name,
       c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
      and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;

I believe you'll need to use dynamic SQL in order to run it in every database. Here's a walkthrough.


Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key I have two tables one is product which is a parent table with one primary key and i do have another child table of product, which is a product_details table. But the child table is linking with parent table(product) with logical data instead of foreign key,as we are doing this relationship with the help of java code in the coding side, instead of depending on the data base, which make it as tight couple.To avoid tight coupling between the tables we are storing the primary key value in the child table.

Please refer this image with data which is creating the logical relationship between Product and Product Details tables.

The Scripts are :

CREATE TABLE `tbl_product` (
  `product_id` varchar(200) NOT NULL,
  `product_details_id` varchar(200) DEFAULT NULL,
  `currency` varchar(20) DEFAULT NULL,
  `lead_time` varchar(20) DEFAULT NULL,
  `brand_id` varchar(20) DEFAULT NULL,
  `manufacturer_id` varchar(150) DEFAULT NULL,
  `category_id` varchar(200) DEFAULT NULL,
  `units` varchar(20) DEFAULT NULL,
  `transit_time` varchar(20) DEFAULT NULL,
  `delivery_terms` varchar(20) DEFAULT NULL,
  `payment_terms` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `tbl_product_details` (
  `product_details_id` varchar(200) NOT NULL,
  `product_id` varchar(200) DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL,
  `landingPageImage` varchar(100) DEFAULT NULL,
  `product_description_brief` text CHARACTER SET latin1,
  `product_description_short` text CHARACTER SET latin1,
  `product_price_range` varchar(50) DEFAULT NULL,
  `product_discount_price` varchar(20) DEFAULT NULL,
  `production_Type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_details_id`),
  UNIQUE KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please suggest me, we are following this kind of relationship in my company, as the manager is saying it will give us flexible to us. Please suggest me the Pros and Cons of the design.I know that if we lose the data from the table, we can't know the relationship between the two tables.

If you want to use relational principles in database design then certainly not.  In a relational design, each entity type in the business domain from the conceptual model is represented by an R-Table in the logical model. The R-Table is a table which, when following a specific discipline, can acquire the properties of a mathematical relational which enable the R-Table to be operated upon with logic statements of arbitrary complexity in an algebraic fashion with guaranteed results.  Entity types at the conceptual level share a set of properties called attributes which describe them.  One or more of these properties is defined to uniquely identify each entity of the class in order to tell them apart.  These are mapped to the R-Table at the logical level as columns, and each Entity mapped to a row.  Each row then represents a predicate, and when values for the columns are entered the predicate is instantiated to become a proposition.

A key component at the conceptual level are the business rules which define exactly what attribute values constitute a true proposition.  These map to constraints in the logical model, which is a key benefit the relational model provides.  When created, the constraints enable the DBMS, which can only manipulate symbols, to effectively keep the data entered consistent with the truth of the real world the data is meant to represent.

So in a nutshell, if you do not declare the constraints - such as a product price must be for one and only one product - to the DBMS, the DBMS will not be able to ensure the data is consistent with the real world.  All bets are then off as to correct query results.

Relational design is not easy however.  It requires detailed analysis of the business domain to be modeled, a full understanding of the business rules that define consistent data, and a careful mapping to the logical level of R-Tables.  All too often what we see in practice is what I call file based design, where tables in the SQL DBMS are used to represent files whose content is based upon totally ad-hoc design considerations.  A clue that the 2 tables in question - product and product details - were designed using file based design is in the name of the product details table.  A file holds details about something.  A Product Details is not an entity type whose class of entities share common properties.  This is also evidenced by the fact that every one of those columns in that table are defined as NULL.

If you want pro's and con's of a file based design I am not the right person to elaborate, as those pro's and con's will be ad-hoc and totally based upon your specific circumstances - such as the technology and the work load in play.

A great primer on relational design and its power can be found in Fabian Pascal's Practical Database Foundation Series.  Fabian lays out the basics for every day practitioners in easy to understand language.  He covers everything I summerized here with clear explanations plus a whole lot more.  I highly recommend it.


Why is SSIS slow to enumerate over many files in a directory and import them? I have a terribly slow SSIS package. It's quite fast with one file, and reasonably fast with 100 files or fewer. (About one second per file)

However, if my directory has thousands of (very small) files, the process drags on terribly slowly. My preference is to run this process only after business hours, but by waiting until then, the number of flat files to import is in the thousands.

The package is very simple:

Outer Loop is For Every (file enumeration, read file path into variable)
Inside, simply import without any transformation to the data

That is it.

Performance with thousands of files is running 15 seconds or more per each file. The UI (status) is drawing/scrolling so slowly that I can't even see where it's at -- the stamped time is more than 15 hours old on an execution that was started 18 hours ago.

Version: MSSQL 2012

I think you're running into a limitation of the UI/debugger.

I created two packages: MakeAllTheFiles and ReadAllTheFiles

MakeAllTheFiles accepts as input the number of files to be created. It will make use of pseudo-random function to distribute the data across a number (7) of sub folder.

MakeAllTheFiles

    public void Main()
    {
        int NumberOfFilesToGenerate = (Int32)Dts.Variables["User::FilesToGenerate"].Value;
        string baseFolder = Dts.Variables["User::FolderInput"].Value.ToString();
        System.Random rand = null;
        int fileRows = 0;
        DateTime current = DateTime.Now;
        int currentRandom = -1;
        int seed = 0;
        string folder = string.Empty;
        string currentFile = string.Empty;

        for (int i = 0; i < NumberOfFilesToGenerate; i++)
        {
            seed = i * current.Month * current.Day * current.Hour * current.Minute * current.Second;
            rand = new Random(seed);
            currentRandom = rand.Next();

            // Create files in sub folders
            folder = System.IO.Path.Combine(baseFolder, string.Format("f_{0}", currentRandom % 7));

            // Create the folder if it does not exist
            if (!System.IO.Directory.Exists(folder))
            {
                System.IO.Directory.CreateDirectory(folder);
            }

            currentFile = System.IO.Path.Combine(folder, string.Format("input_{0}.txt", currentRandom));

            System.IO.FileInfo f = new FileInfo(currentFile);
            using (System.IO.StreamWriter writer = f.CreateText())
            {
                int upperBound = rand.Next(50);
                for (int row = 0; row < upperBound; row++)
                {
                    if (row == 0)
                    {
                        writer.WriteLine(string.Format("{0}|{1}", "Col1", "Col2"));                        }

                    writer.WriteLine(string.Format("{0}|{1}", row, seed));
                }
            }
            ;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

ReadAllTheFiles

The general appearance of the package is thus

I have two Connection Managers defined: One is to my database and the other is to a Flat File with an Expression on the ConnectionString property such that it uses my Variable @[User::CurrentFileName]

Variables, I like lots of Variables so there are plenty.

My Execute SQL Task simply stands up a table for me to write to, knocking it down if it already exists.

IF EXISTS
(
    SELECT * FROM sys.tables AS T WHERE T.name = 'dbase_54462' AND T.schema_id = SCHEMA_ID('dbo')
)
BEGIN
    DROP TABLE dbo.dbase_54462;
END

CREATE TABLE
    dbo.dbase_54462
(
    CurrentFile varchar(256) NOT NULL
,   Col1 int NOT NULL
,   Col2 varchar(50) NOT NULL
,   InsertDate datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP)
);

My Foreach Enumerator simply looks at everything in my Input folder based on the file mask of *.txt and traverses subfolders. The current file name is assigned to my variable @[User::CurrentFileName]`

The Data Flow is bog standard. The Derived Column Transformation there simply adds in the Current File Name variable into the data flow so I can record it in my table.

Analysis

I'm lazy and didn't want to do anything special to record processing times so I deployed my packages into the SSISDB catalog and ran them from there.

This query looks at the catalog data to find out how long the package ran, how many files it processed and then generates a running average for file count. Run 10047 was bad and was excluded from analysis.

SELECT
    E.execution_id
,   DATEDIFF(s, E.start_time, E.end_time) As duration_s
,   ES.rc AS FilesProcessed
,   AVG(ES.rc / (1.0 * DATEDIFF(s, E.start_time, E.end_time))) OVER (PARTITION BY ES.rc ORDER BY E.execution_id) AS running_average
FROM
    catalog.executions As E
    INNER JOIN
    (
        SELECT
            MIN(ES.start_time) As start_time
        ,   MAX(ES.end_time) AS end_time
        ,   count(1) As rc
        ,   ES.execution_id
        FROm
            catalog.executable_statistics AS ES
        GROUP BY
            ES.execution_id
    ) AS ES
    ON ES.execution_id = E.execution_id
WHERE
    E.package_name = 'ReadAllTheFiles.dtsx'
    AND E.execution_id <> 10047
ORDER BY 1,2

The resulting data (gratuitous SQLFiddle)

execution_id    duration_s  FilesProcessed  running_average
10043   15  104 6.93333333333333
10044   13  104 7.46666666666666
10045   13  104 7.64444444444444
10050   102 1004    9.84313725490196
10051   101 1004    9.89186565715395
10052   102 1004    9.87562285640328
10053   106 1004    9.77464167060435
10055   1103    10004   9.06980961015412
10056   1065    10004   9.23161842010053
10057   1033    10004   9.38255038913446
10058   957 10004   9.65028792246735
10059   945 10004   9.83747901522255

Based on this sampling size, I see no appreciable difference between processing 100, 1000 or 10,000 files with SSIS as described herein.

Root cause assumption

Based on the comment about DTExecUI.exe that says you're running the package from within Visual Studio (BIDS/SSDT/name-of-the-week). To get the pretty color changes and debugging capability, the native execution (dtexec.exe) is wrapped up in the debugging process. That creates an appreciable drag on execution.

Use the design environment to create your packages and to run them for smaller data sets. Larger ones are best handled through the non-graphical & non-debugger execution interfaces (shift-F5 in VS, deploy to SSIS catalog and execution from there, or shell to the command line interface and use dtutil.exe)


What is a WITH CHECK CHECK CONSTRAINT? I have some auto-generated T-SQL, which is probably valid, but I don't really understand.

ALTER TABLE [dbo].[MyTable]
WITH CHECK
CHECK CONSTRAINT [My_FORIEGN_KEY];

I know what a foreign key constraint is, but what's the CHECK CHECK?

The MSDN documentattion page about ALTER TABLE explains these:

ALTER TABLE: modify the table's structure
(and some of the possible actions/modifications are):

CHECK CONSTRAINT ..: enable the constraint
NOCHECK CONSTRAINT ..: disable the constraint
There are also additional, optional steps to do while creating/enabling/disabling a constraint:

WITH CHECK: check the constraint as well
WITH NOCHECK: do not check the constraint

In their words:

| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
    { ALL | constraint_name [ ,...n ] }

  ...

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

  If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

  The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  ...

  { CHECK | NOCHECK } CONSTRAINT
  Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

Test in dbfiddle:

CREATE TABLE a (aid INT PRIMARY KEY);

  GO

✓

INSERT INTO a (aid)
VALUES (1), (2), (3) ;

  GO

3 rows affected

CREATE TABLE b
( aid INT,
  bid INT PRIMARY KEY,
  CONSTRAINT [My_FORIEGN_KEY]
    FOREIGN KEY (aid) REFERENCES a (aid)
) ;

  GO

✓

INSERT INTO b (aid, bid)
VALUES
  (1, 11),
  (1, 12),
  (2, 21),
  (3, 31) ;

  GO

4 rows affected

INSERT INTO b (aid, bid)
VALUES
  (6, 61),
  (6, 62) ;

  GO

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31

ALTER TABLE b NOCHECK CONSTRAINT [My_FORIEGN_KEY];   --disable

  GO

✓

INSERT INTO b (aid, bid)
VALUES
  (4, 41),
  (4, 42) ;

  GO

2 rows affected

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

ALTER TABLE b WITH NOCHECK CHECK CONSTRAINT [My_FORIEGN_KEY];
-- enable constraint without checking existing data

  GO

✓

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

INSERT INTO b (aid, bid)
VALUES
  (6, 61),
  (6, 62) ;

  GO

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

ALTER TABLE b WITH CHECK CHECK CONSTRAINT [My_FORIEGN_KEY];
-- check existing data and enable constraint

  GO

Msg 547 Level 16 State 0 Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.


copying data from a SQL server-table to a SQLCE-table I want to copy some data from a MSSQL table to a SQLCE table in database.net.

I used the following query, connected to the MSSQL db:

update [SQLCE-table] ([column])
SET (column) FROM [MSSQL]

I keep getting several error messages like token in error = (].
What am I doing wrong?

After reading the comment, I would like to suggest that you can use SQL Server Compact bulk copy library

Check sqlcebulkcopy.codeplex.com
Or you can use ErikEJ’s blog post to migrate and the utility SQLCMD.
Or create Integration Services packages that write data to SQL Server Compact databases check link


Do I need to VALIDATE CONSTRAINT? I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.
From the documentation:

But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

It doesn't mention the consequences – not even in the notes –, if there are any at all.
Does it matter if a constraint is not marked as valid forever?

I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.

Why?
The constraint will be applied to all new data as it is added.
The "not valid" option allows the constraint to exist whilst ignoring any historically-entered, bad data.
If you have no such bad data, then the "not valid" option serves no useful purpose and, indeed, is misleading to anyone else looking at the constraint, who will assume that it's been configured this way to allow for bad data (that isn't there!).


Can we create a stored procedure to copy detached .mdf and .ldf files from original location to new location? I am trying to create a script to attach database, move .mdf and .ldf files to new location and then attach the database. I created a stored procedure to detach and attach the database. Can I get a suggestion as to how I can move the files from the script itself?

Use master;
Go
Create procedure User_sp_detach_attach
    @db_name varchar(max),
    @new_mdf_path varchar(max),
    @new_ldf_path varchar(max)
As
Set NoCount On;
declare @db_id smallint;
declare @mdf_file varchar(max);
declare @ldf_file varchar(max);
declare @ori_mdf nvarchar(max);
declare @ori_ldf nvarchar(max);
declare @new_mdf_file nvarchar(max);
declare @new_ldf_file nvarchar(max);
declare @sql varchar(max);
set @db_id=DB_ID(@db_name);
set @mdf_file=@db_name+'.mdf';
set @ldf_file=@db_name+'_log.ldf';
select @ori_mdf=physical_name from sys.master_files where database_id=@db_id and file_id=1;
select @ori_ldf=physical_name from sys.master_files where database_id=@db_id and file_id=2;
set @new_mdf_file=@new_mdf_path+'\'+@mdf_file;
set @new_ldf_file=@new_ldf_path+'\'+@ldf_file;
print 'New location of .mdf file: '+@new_mdf_file;
print 'New location of .ldf file: '+@new_ldf_file;
EXEC sp_detach_db @db_name;
--Move files
Set @sql='CREATE DATABASE ' + @db_name + ' ON (FILENAME=''' + @new_mdf_file + '''), (FILENAME=''' + @new_ldf_file + ''') FOR ATTACH;'
Print @sql;
Exec (@sql);
Print 'Moved and attached successfully';
Go

Well you can use xp_cmdshell, write (or find and use) a CLR routine to do file copies and things like that. For example, the unrecomnended use of xp_cmdshell.

EXEC xp_cmdshell "COPY \\share\path\file.mdf \\othershare\otherpath\file.mdf"

However, you would be better just making a backup (which from SQL Server 2008 R2 supports compressed backups) and you will get a faster transfer than detaching and copying files.  Not to mention the risks of detaching a file and not being able to reattach.

Recommendation: Use BACKUP and RESTORE.


How do I swap tables in MySQL? Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.

That's why I want to compute more recent statistics in foo_new and swap them when computation is ready.

I could do

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

but what happens if a query needs table foo inbetween those two lines when there is no table foo? I guess I have to lock it somehow... or is there another way to do it?

Use this one command:

RENAME TABLE foo TO foo_old, foo_new To foo;

It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.


MySQL: querying for latest messages in flat reply system I've created a message system schema in MySQL as follows. It consists of two tables (messages, recipients).

`Message_ID` int(11) NOT NULL AUTO_INCREMENT,
`Message_Sender_ID` int(11) NOT NULL,
`Message_Sent_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Message_Subject` varchar(255) NOT NULL,
`Message_Text` text NOT NULL,
`Message_Sender_Keep` boolean DEFAULT TRUE,
`Message_Root_ID` int(11),
PRIMARY KEY (`Message_ID`)

`Recipient_Message_ID` int(11) NOT NULL,
`Recipient_User_ID` int(11) NOT NULL,
`Recipient_Keep` boolean DEFAULT TRUE,
`Recipient_Read_Date` timestamp DEFAULT NULL,
PRIMARY KEY (`Recipient_Message_ID`, `Recipient_User_ID`)

Here's how it works. When a user creates a new message, the message's root_id is set to it's own id. When someone replies, the root id is set to the root message of that thread. The recipient information is also stored in the recipients table.

What I'm stuck on is querying the latest message of each unique thread. Instead, it's returning all of the unique threads, but only the root messages (I want the newest ones).

SELECT
  M.*,
  R.*
FROM messages M INNER JOIN recipients R
  ON M.Message_ID = R.Recipient_Message_ID
WHERE (R.Recipient_User_ID = $user_id OR M.Message_Sender_ID = $user_id)
GROUP BY M.Message_Root_ID HAVING MIN(M.Message_Sent_Time)
ORDER BY M.Message_Sent_Time DESC

I've been trying to figure this out for myself, but I've just looked at it for way too long. Anyone able to help me on this?

You can find the latest sent message in each thread and then join that with your tables:

select M.*, R.*
FROM messages M
JOIN recipients R
    ON M.Message_ID = R.Recipient_Message_ID
JOIN (
    select Message_Root_ID, max(Message_Sent_Time) as Message_Sent_Time
    from messages
    group by Message_Root_ID
) as X
    ON M.Message_root_id = X.Message_root_id
   AND M.Message_Sent_Time = X.Message_Sent_Time


Using Dynamic Queries Across Multiple Databases to Change Views I am seeking to run a query to modify views across multiple databases (See the initial question here.) and was able to find how to get it.  I've now run into the problem that if I run this from master (Where I'm sure it has to run from) i receive null results.  However, if i go to a specific database and run the dynamic query there, i get the views i'm looking to modify (the intended result).

This is the action I want performed on each database:

select @strResult = definition from sys.sql_modules where object_id = object_id('view')

What do i need to do to ensure that I get this information back?

The call needs to be dynamic for the databases, as does the object_id.

This is basic (does not check for offline databases and the like), but gives you an idea as to how it would function, looping through the databases in sys.databases.

DECLARE @viewDef NVARCHAR(max);
DECLARE @DatabaseList sysname;
SELECT @DatabaseList = MIN(name) FROM sys.databases;
WHILE @DatabaseList IS NOT NULL
BEGIN

SELECT @viewDef = 'SELECT Definition from ' + QUOTENAME(@DatabaseList) + '.sys.sql_modules WHERE OBJECT_ID = OBJECT_ID(N'+CHAR(39)+QUOTENAME(@DatabaseList) +'.dbo.view'+CHAR(39)+');'
--PRINT @viewDef
EXEC (@viewDef)

SELECT @DatabaseList = MIN(name) FROM sys.databases WHERE name > @DatabaseList;
END


INSERT INTO without duplicates I want to do this

INSERT INTO AdminAccounts (Name)
SELECT Name
FROM Matrix

But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.

This will insert new Name fields that were not previously in the Matrix table.

INSERT IGNORE
INTO AdminAccounts (Name)
SELECT Name
FROM Matrix;

If you do not trust INSERT IGNORE, there is an alternative where you can manifest the new Name values before inserting them:

CREATE TABLE NewName SELECT Name FROM Matrix WHERE 1=2;
INSERT INTO NewName
    SELECT Name FROM AdminAccounts A
    LEFT JOIN Matrix B USING (Name)
    WHERE B.Name IS NULL;
INSERT INTO Matrix (Name) SELECT Name FROM NewNames;

The table NewName collects only those tables in AdminAccounts that are not in Matrix at present. This can give you a chance to look over the new Names. Afterwards, you can INSERT everything in NewName into Matrix.


Return text from PostgreSQL (9.6) to SQL Server from linked server query I am trying to get text (15002 characters long) from PostgreSQL using a linked server from SQL Server 2012, using the official PostgreSQL ODBC driver in Windows.
I try to pull data from Postgres using the following query:
create table #temp
(id int, g nvarchar(max) )
insert into #temp
exec ('select id ,st_astext(geom) from spatialdata') at pg96odbc

This returns correct length data, but the text is truncated to ~4000 characters; the rest of the row contains null values. I see this using the following query (on SQL Server):
select id, g from #temp for xml auto

This works correctly in the other direction (Postgres pulling from SQL Server) using odbc_fdw and the SQL Server native driver.
Question: Is this a bug, or is there an ODBC/SQL Server option to get it to return all data, or a way to return varbinary(max) from the Postgres server?
I'm using BigSQL PostgreSQL 9.6, the latest ODBC driver for PostgreSQL 9.6, and the latest odbc_fdw.
FOR XML AUTO returns data containing this: (&\#x0; = null). Length of string is more or less correct, source geom is linestring.
MaxVarcharsize = -4:

Cannot get the current row value of column "[STREAM].geos" from OLE DB provider "STREAM" for linked server "(null)". [COLUMN_NAME= geos  STATUS=Unknown DBBINDSTATUS]

MaxLongVarcharSize = 32k or 8k
(first "working character string is 4000 after it comes null values up to "real" length of text) example output below:
LINESTRING(2.773577 3.849574,2.773598 ... ,2.603447 3.847&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x4;&#x0;&#x0;&#x0;ꪝ&#xF;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;⎮&#x0;&#x0;&#x0;
LINESTRING(2.905497 0.547610999999996,2.905621 0.547591999999995,...,2.894462 0.5&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x4;&#x0;&#x0;&#x0;竿&#x10;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;ᔺ&#x0;&#x0;&#x0;
LINESTRING(3.608147 2.496786,3.607717 2.496774,3.607176 2.496804,3.605301 2.497387,3.603758 2.49786999999999,3.602421 2.49828599999999,3.600669 2.49880499999999,3.59832 2.49952,3.596655 2.500024,3.594704 2.500643,3.592416 2.50134899999999,3.590898 2.50182599999999,3.590145 2.50205199999999,3.589244 2.502335,3.588036 2.502623,3.586846 2.50291299999999,3.584997 2.503305,3.583173 2.503676,3.581646 2.503925,3.580145 2.50402699999999,3.578866 2.504097,3.576313 2.504255,3.574554 2.504361,3.572587 2.50447699999999,3.57084 2.504583,3.568731 2.504699,3.566653 2.50480999999999,3.565399 2.504848,3.564418 2.50494399999999,3.562436 2.50506,3.561114 2.505138,3.559385 2.50509199999999,3.558475 2.50496699999999,3.5580

Next thing to try is to change driver from 9.06.01.00 to something else.

ODBC Problem

I think this is a function of the PostgreSQL ODBC driver

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL]
"MaxLongVarcharSize"="4094"

Changing that with regedit may work (no tabs or spaces around =). However, there may be other drawbacks.

This is also documented in the psqlODBC Configuration Options..

  Max LongVarChar: The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value.

I believe setting this to SQL_NO_TOTAL (ie., -4) means that the ODBC driver has to malloc rather static allocate the col size.

GIS sub-optimal

From the PostGIS angle, you should be using Well-Known-Binary via ST_AsBinary and not storing the WKT as nvarchar, but instead storing the geometry from WKB in SQL Server.


How to convert decimal number to IP address? I need a function or query to convert a decimal number to ann IP address.

Presently my ip_address column looks like this:

select  ip_address from test_ip;

    ip_address

     173015430
     173015318
     927815779

I need the result like this:

         ip_address
        10.80.1.134
        10.80.1.22
        55.77.88.99

I can do this in MySQL by using INET_NTOA function. How can I accomplish this in Oracle?

I am using these two functions in my application:

CREATE OR REPLACE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER;
BEGIN
    SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
    INTO DecimalIp
    FROM dual
    CONNECT BY LEVEL <= 4;
    RETURN DecimalIp;
END Ip2Decimal;

and

CREATE OR REPLACE FUNCTION Decimal2Ip(IpDecimal IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS

    NUMERIC_OVERFLOW EXCEPTION;
    PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;
BEGIN
    IF IpDecimal > 2**32 -1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;
    FOR i IN 1..4 LOOP
        Octet := TRUNC(v_IpDecimal/256**(4-i));
        v_IpDecimal := v_IpDecimal - Octet * 256**(4-i);
        IP := IP ||'.'||Octet;
    END LOOP;
    RETURN SUBSTR(IP, 2);
END Decimal2Ip;


Reconnect "not connected" query windows in SSMS to original connection Is there a simple way to reconnect a query window that is "not connected" to the original connection that was used by that query without manually picking the server name needed? If I click the Connect button on the SQL Editor toolbar or right click -> Connection -> Connect... it defaults the server name to the last connection I used in that SSMS session, not to the last connection used by that specific query window.

After taking my laptop home at the end of the day, all the open windows will be set to not connected the next time I use it. Ideally, I'd like to be able to reconnect all to their original connections, but I don't know that even the one query at a time is possible.

In SSMS, if you run a query in a disconnected window, it will attempt to re-connect to the prior connection. You will get an error, then the window will reconnect.  If you run a query like SELECT 1;, it will reconnect without having to do any data access/manipulation.

However, I'm a bit timid about relying on this to remember what server I was previously connected to. While I have noticed this to be the behavior, it is not documented, so I do not fully trust that it is "as designed" vs "the way it works." You should always make sure you are connected to the correct server before running any scripts.


Move Mysql database from Windows Xampp to Linux MySql Server I 'am looking for a way to move some database in my local xampp to remote MySql Server , running into a Linux VM (Centos 7)

Any idea or any suggestions to do this migration?

One way you could do it is by using HeidiSQL, a free MySQL client. You can get it here:

https://www.heidisql.com/

You can configure the connection to the Windows XAMPP server and the Linux server, and then Heidi will be able to transfer the data directly from one server to the other. To do that:

connect to the "source" server
right-click on the database or table you want to transfer and select "Export Data As SQL"
this will open a dialog box
you likely will want to select the checkboxes to create the tables and databases you want to transfer if it's the 1st time you have transferred them
if you want the data and not just the structure, be sure to select the data dropdown to transfer the data as well. I usually use "Delete + Insert", but your needs may vary
in the output dropdown, select the "destination" server. In this case, it should be your Linux server
In the Directory dropdown, you likely will want to leave it as [same as source server]
Then click the Export button and wait


Ora 01017 Invalid Username Password; with correct data I am new to oracle (enterprise:12.2.0.1) and after installation I logged in with connect sys as sysdba.
I also created a new user with:
 alter session set "_ORACLE_SCRIPT"=true;
 create user dummy identified by dummy;
 GRANT ALL PRIVILEGES TO dummy;

Everything went successful until now, but when I tried to log in with the new created user with 'connect dummy' I get the error: Ora 01017 Invalid Username Password; logon denied
After resetting the password 10 times and creating 8 new users, I am nearly 100% sure, the password and username is correct.
I read, that this problem can also appear because of the tnsnames.ora file, but I don't try to connect with TNS, so I would rule this case.
After many hours, I would now be happy, if I can get here a simple step-by-step solution and explanation.
Thank you very much and greetings from Germany.

I reinstalled oracle and now it works fine.
My guess with tnsnames.ora was totally wrong and also think there was the error!


Is it worth partitioning a Table by single value in Oracle? Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in advance? Let's say you have a table AUDIT with TenantId column which could only containt values: 1, 2.
All SELECT statements would have a WHERE clause with a TenantId parameter.

So, would it be beneficial to partition this table by TenantId? If so, would you also create an index on TenantId column?

partition by list (TENANTID)
(
  partition TENANT1 values (1),
  partition TENANT2 values (2)
)

I have done a little experiment: inserted 1M records with TenantId randomly generated, thus creating:

499652 records with TenantId=1
500348 records with TenantId=2

Here are the query plans for statement:

SELECT * FROM table1 WHERE TENANTID=2

Plain Table (no indices, no partitions)

Bitmap Index:

Partitions:

Btw, in case where I have both, the index and partitions, query plan uses partition and not index, hence the plan looks exactly the same as the 2nd one shown above.

Clearly the partition wins, but does it?
Apparently, the cost column in an execution plan is not a reliable way to judge the real costs of a SQL statements response time.

So, what is the best way then? How to choose one over another?

My opinion is that partitioning of that table in the manner presented may be useful.

Your queries will be two times faster (in the case when partitions are almost equal) if they make full scans.

In case your queryes have another tone of filters/conditions and use indexes, partitioning is not useful, because the level of an index is almost not affected by doubling the number of values.

UPDATE: For the test you did (SELECT * FROM table1 WHERE TENANTID=2) it is sure that partitioning is best. The bitmap solution need to scan the index, and after this, to scan all table's blocks containing rows with tenantid=2(it knows what are the rows). But partitioning will cause just scanning the table's partition with tenantid=2. They are phisically separated to tenantid=1.

So, two scans(index scan + table scan) vs one table scan(wich may be smaller).


Select top-most folder in a hierarchy by given criteria in MySQL I'm using MySQL (5.7.20) to store a folder hierarchy. Each folder is stored together with all direct and indirect parents including the distance. As an example, the following folders

A
+- B
   + C
   + D

would be stored like this:

parent | child | dist
-------+-------+-----
A      | A     | 0
A      | B     | 1
A      | C     | 2
A      | D     | 2
B      | B     | 0
B      | C     | 1
B      | D     | 1
C      | C     | 0
D      | D     | 0

Additionally, there is another table that links a folder to one or many users, like this:

child | user
------+-----
A     | U1
B     | U1
C     | U2
D     | U2

What I'm now trying to achieve is to query this hierarchy by user in a way that only the highest matches in the hierarchy are returned.

So as in the example above, if I query for folders of user U1, I only want A as a result, because the second match B is a child of A. On the other hand, if I query for folders of user U2, the result should be C, D as both folders are on the same hierarchy level.

The closest query I could imagine was SELECT child, dist AS d FROM folders JOIN users ON folders.child = users.child GROUP BY folders.parent HAVING d = min(d); but unfortunately it is not giving the right results, as for example a query for user U1 would still give folders A,B as a result.

Does anyone have an idea how this could be done?

I think you want this. All the results from users table except those that have a parent in users:

SELECT u.user, u.child
FROM users AS u
WHERE NOT EXISTS
      ( SELECT 1
        FROM users AS u2
             JOIN folder AS f
             ON f.parent = u2.child
        WHERE f.child = u.child
          AND f.dist > 0         -- this could be: AND f.parent <> f.child
          AND u2.user = u.user
      ) ;


Windows Server and SQL Server updates and patching - and how to rollback (revert) if necessary Say we have a server as SQL Server 2017 on Windows Server 2016, both have some pending updates recently

Below few questions:

What's the correct order of installing updates - first Windows, and then SQL Server ? vice versa ?
Or the order does not matter ?
How do I know if my Windows or SQL Server update failed ?
Is it possible that any of updates can fail and bring server to an unusable state ?
How to rollback (revert) Windows or SQL Server update if it was unsuccessful
(or even broke the system) ?

What's the correct order of installing updates - first Windows, and
  then SQL Server ? vice versa ? Or the order does not matter ?

There is unlikely to be any prerequisite for a Windows update that requires specific SQL Server updates to be applied because not all Windows Servers run SQL Server, however, it is possible that you may need a specific Windows update for a given SQL Server update (none spring to mind but it is conceivable). So given this, applying Windows Updates first is probably your best bet.

NOTE: Windows updates often set the restart pending flags and this can cause SQL Server update installers to fail the prerequisite checks. You need to orchestrate your patching process to handle restarts between applying patches if required.

  How do I know if my Windows or SQL Server update failed? Is it
  possible that any of updates can fail and bring server to an unusable
  state?

While it is possible for a SQL Server update to cause a fault that makes SQL Server unusable, it isn't too common. There are examples of it occurring though, check out Aaron Bertrand's blog article on updates for older SQL Server versions to support TLS 1.2 - SQL 2008 and 2008 R2 had a number of early issues causing service interruptions once the TLS 1.2 patch was applied.

For confirming the outcome of your SQL update installation, you need to review the setup log files which will provide a summary status that should confirm if the installation was successful. Generally, you want to check the summary.txt file and confirm the outcome is reported as "Passed" or "Passed, but requires reboot".

For Windows updates, you want to review the CBS.log file for Windows Update installations.

  How to rollback (revert) Windows or SQL Server update if it was
  unsuccessful (or even broke the system)?

Typically, updates can simply be uninstalled from the server to revert to the previous state. This can be done via Programs and Features (for SQL Updates, for SQL 2008+) and Windows Update Center (for Windows updates)

In the case of fatal errors preventing the server from booting, you can take a snapshot of the VM prior to applying updates that you can use for rollback.


Execution Plan StatementText contains incorrect syntax I have run an execution plan with management studio 2014 on mssql 2008 R2.
To be able to better read the Selectstatement i right-clicked the plan to view it in xml.

I noticed that some statements seem to contain invalid charachters SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable. At least if i copy them into a new query window i get an invalid syntax error

  <StmtSimple StatementCompId="10" StatementEstRows="1"
  StatementId="2" StatementOptmLevel="TRIVIAL"
  StatementSubTreeCost="0.0032831"
  StatementText="SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable
   WHERE Id = @VersionID&#xD;&#xA;&#xD;&#xA;
  -- if head  and no delta, select platformIDs and vehicleIDs in this version&#xD;"
   StatementType="SELECT" QueryHash="0x29CDF8384A77017F" QueryPlanHash="0xF39275675F085D1B">
   <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true"
        ANSI_WARNINGS="true" ARITHABORT="true"
        CONCAT_NULL_YIELDS_NULL="true"
        NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

The Statment is dynamically generated inside a stored procedure.

My qustion is could the invalid characters be added by management studio in the xml view?

Or do i have incorrect escape sequences inside the generated sql

&#xD;&#xA; are carriage return/line feed. You could use SSMS find/replace to either remove them, or set them to start new lines. Just use the regular expression option.


Shortcut for deleting a line of SQL Is there a shortcut to delete your current line of SQL without highlighting the full line?  I want to delete the line that my cursor is on without selecting the whole line.

So in visual studio CTRL + L will delete the full line.


Safe online dumping of an InnoDB database in the presence of DDL changes When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options.  This allows for minimal blocking of other concurrent reads + writes to the database.

However, there is the following nasty gotcha in the documentation:

  While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

My backups are automated and periodic.  I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.

So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?

I believe that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.

I'm wondering if there is a happy middle ground between the two approaches?  Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables, or even maybe --lock-all-tables, instead of --single-transaction --skip-lock-tables?

Thanks!

You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.

As table modification isn't transactional, changing the isolation level won't help.

If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.


Unable to connect to postgres inside a docker container I've started a new postgres:12 docker instance as:
$ docker run -it -d --name newdb \
    -p 5444:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_USER=postgres \
    --volumes-from dbstore \
    -v /mnt/data/dbbackup:/backup postgres:12 bash \
    -c "cd /dbtestdata && tar xvf /backup/20210206_backup.tar --strip 1 && /bin/bash"

Then I attached to it:
$ docker exec -it newdb /bin/bash

Created a cluster:
# pg_createcluster 12 main

Which led to:
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Started the service:
# service postgresql restart
[ ok ] Restarting PostgreSQL 12 database server: main.

Tried both ways to connect, but none was successful:
# psql -U postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

# psql -U postgres -W
Password: ******* <<-- postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

What could I have done wrong?

postgres peer authentication depends on the linux/unix user that runs psql being the same as user in the -U postgres parameter. As you are running on root locally this isn't true.
Having a postgres user outside the container won't necessary map to the postgres user inside the container.
Recommend changing authentication mechanisms


Refactoring Series of UNIONs in single table (SQL/SYBASE) I am trying to query a database and reformat the rows/columns that I am interested in. Each row in the current table (EQ) represents a field in a form. In reality, there are several hundred FieldNum values; each EntityID "has" every FieldNum. Each FieldNum has a corresponding MemoText value. In my example below, FieldNum 1,2,3 are related, as are 4,5,6.

EQ and the resulting table should look like:

I need to create a single row out of multiple FieldNum values, but I am unsure how to do this without using UNION ALL. It seems incredibly inefficient to use seven or eight UNION statements, considering all the data I am interested in is located in a single table. Alas, I am unsure how to refactor the code to make it more efficient. A sample of my code is below.

Select En.EntityNum , En.EntityID,
Test.Date, Test.Name1, Test.Name2

FROM En
JOIN
(SELECT EntityNum,
Max(Case WHEN FieldNum = 1 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 2 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 3 Then Memotext End) as Name2
FROM EQ  WHERE Group by EntityNum
UNION ALL
(SELECT EntityNum,
Max(Case WHEN FieldNum = 4 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 5 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 6 Then Memotext End) as Name2
FROM EQ  WHERE
Group by EntityNum
FROM EQ Group by EntityNum
) as Test ON test.EntityNum = En.EntityNum

I tried to generalize this question as much as possible, but if it is still too specific, a generalized answer would be completely satisfactory.

If you subtract 1 from FieldNum and divide the result by 3 using integral division, you will get the following results for FieldNum values of 1 till 6:

FieldNum | (FieldNum - 1) / 3
---------+-------------------
    1    |         0
    2    |         0
    3    |         0
    4    |         1
    5    |         1
    6    |         1

And if you replace / with % (the modulo operator), the results will be like below:

FieldNum | (FieldNum - 1) % 3
---------+-------------------
    1    |         0
    2    |         1
    3    |         2
    4    |         0
    5    |         1
    6    |         2

Now, in case the idea isn't yet clear, the (FieldNum - 1) / 3 expression can be used as a grouping criterion in addition to EntityID (for some reason called EntityNum in your query). And the results of (FieldNum - 1) % 3 could be used to determine particular rows (would-be columns) within the subgroups defined by (FieldNum - 1) / 3.

Finally, if my attempts at verbal description hasn't really succeeded, here's a query that uses the above two expressions to produce the results you are after:

SELECT
  EntityID,
  MAX(CASE (FieldNum - 1) % 3 WHEN 0 THEN Memotext END) AS Date,
  MAX(CASE (FieldNum - 1) % 3 WHEN 1 THEN Memotext END) AS Name1,
  MAX(CASE (FieldNum - 1) % 3 WHEN 2 THEN Memotext END) AS Name2
FROM EQ
WHERE FieldNum BETWEEN 1 AND 6
GROUP BY
  EntityID,
  (FieldNum - 1) / 3
;

And here you can find a "live" SQL Fiddle demonstration. To be honest, the demo uses SQL Server to run the query, but I would expect Sybase to produce the same results. (I'm assuming that FieldNum is an integer-typed column and that Sybase, like SQL Server, automatically applies integral division when both operands of / are integers.)


Why MySQL imports only one record for each table? Code shows some info:

$ cat /etc/*-release | grep VERSION
6:VERSION="12.04.5 LTS, Precise Pangolin"

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2

$ mysqldump -uUSER -pPASS DBNAME > dump.sql

$ ll dump.sql
-rw-r--r--  1 root    397M Dec 29 01:13 dump.sql

$ mysql -uUSER -pPASS DBNAME < dump.sql

MySQL don't show any errors but after import i have only first rows in all tables.
I've checked with head sql syntax and it seems to be correct.

I've tried also mysqldump -uUSER -pPASS DBNAME | gzip -9 > dump.sql.gz and got same situation.

Why this is happening?
And how to fix?

Thanks.

I'm using Mac OS (10.10.1) and SequelPro (1.0.2).

This is bug in SequelPro, i think: bug began to appear after upgrading OS to 10.10.1.

See issue #2048 on GitHub.


Redshift DISTSTYLE ALL table takes less disk space? (Self-migrating from StackOverflow.)

We have a fat table of exactly 591 columns in Redshift, distributed by a key.

We were unaware that fat tables chew up space - 2 blocks per column per slice, minimum, and we aren't close to filling up the first block on any slice.

I misunderstood a coworker to recommend using "DISTSTYLE ALL." That seemed maddeningly counter-intuitive, but I set about trying this, and got a surprise that the disk usage of the new table, with the same rows and columns, is actually 1/20th of the size of the original table.

I'm using this query to get the sizes, but Aginity agrees when reporting disk usage for these tables:

select trim(pgdb.datname) as Database,
       trim(a.name) as Table,
       b.mbytes
   from stv_tbl_perm a
   join pg_database as pgdb on pgdb.oid = a.db_id
   join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes,
                count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
join ( select sum(capacity) as  total
  from stv_partitions where part_begin=0 ) as part on 1=1
where a.slice=0 and pgdb.datname = 'my_schema'
AND a.name IN ('my_table',
               'my_table_distall')
order by Database, Name;

Yielding this output:

Schema       Table                mbytes
my_schema    my_table             222080
my_schema    my_table_distall      10740

The reason I mentioned the exact number of columns is that 222080=594*5*32*2, which means we use two blocks for every 5*32 slices for every column (including Redshift's three hidden columns.)

The new table, with DISTSTYLE ALL, is taking up an average of 3.6 block per column per node.

This is very confusing. Why is my new table smaller by a factor of 20?

Due to the size of our data, I actually didn't expect it to get bigger, because even with all the data, we would not take up more than one block per slice per column, but smaller seems insane.

Is it just as simple as "DISTSTYLE ALL duplicates the table to one slice on each node?" So I only have one or two blocks used per node and column, rather than one block or so of usage per slice and column?

Well, after lots of experimentation, we have found that, yes, if you have a fat table with not enough rows to fill much of a block at the slice level using DISTSTYLE KEY, it is much more efficient, space-wise, to use DISTSTYLE ALL, even though this replicates more data.

For example, our fat tables have, say, 600000 rows and 1000 columns. Then, on a 5-node 32-slice setup, they were using up 1Mb per column per node per slice, or 160 Gb, and we were only filling less than 1% of that space.

DISTSTYLE ALL appears to not use all the slices.

So using DISTSTYLE ALL, we multiplied the data by 5, but this still takes up less than 1/20th of the space that the DISTSTYLE KEY tables did.

It slows down our queries a little (due to the need for IPC between the slices, and because the DISTSTYLE KEY means we have only the data on the node that matches our other data) but, since these tables are loaded once and never change after, we don't suffer from the other problems of DISTSTYLE ALL.


At What Point Should I Split or Partition a Very Large but Simple Table Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day.

The hosting provider has suggested that we split or partition the tables, and I have seen this recommendation elsewhere on numerous occasions.

However...

I am struggling to reconcile this advice with the stated max capacity for SQL Server - a database size of 524,272 terabytes, with table rows limited only by "available storage".

Based upon those figures, the table described above could easily have centillions of rows (10 to the power of 303).

Ah ha you might say, there is a difference between CAPABILITY and PERFORMANCE.

But in virtually every question about SQL Server performance the answer is "It depends.... on table design and query design".

That is why I am asking this question. The table design couldn't be much simpler. Nor could the queries which are simple count(*) operations based on an indexed ID field.

There's a reason that the general advice is that it depends on the table design and the queries on it. My answer to your other post on Stack Exchange says as much. Saying "queries which are simple count(*) operations based on an indexed ID field" doesn't give much information since it says nothing of the cardinality of the set of rows under consideration. Things you can do to mitigate the (as of now perceived) problems are:

Partitioning. Specifically, your data seems to be logging-type data. My guess is that you want to get stats by some unit of time (e.g. "widgets per day" or "whozits by hour"). Partition by your quantum (i.e. days or hours in the previous examples) and move partitions off to read-only file groups occasionally
On a related note, if the data is write-once, consider pre-aggregating the data once the time period is no longer active. That is, why do I need to keep counting how many events happened on a day from three years ago if that data is never going to change? Once the day is over, count everything in that day, store it somewhere else, and never count it again. In fact, if you never have need of the detailed data (i.e you only ever do aggregations against it), consider deleting it after you count it. If you implement this idea, you can get even more clever with filtered indexes that cover only the "active" period which will make your queries faster because they will not cover the vast majority of your data

But, as my advice in the other post suggests, the only way you're going to know for sure is to load it up with a reasonable amount of data and try it out. All we can do here is say what will probably work in the general case. Without the specifics of your hardware, your data, and your queries, all we can do is guess. And, you may find that once you run the test that I'm proposing that the answer is "there's nothing to do" because it works just fine as is.


Strange behaviour of a query with order by and rownum on Oracle's Exadata server I am executing the following query on two different database servers:

SELECT start_date,
  end_date,
  resort_id
FROM employee_activity
WHERE employee_id = 27
  AND start_date < to_date('2012-11-09', 'YYYY-MM-DD')
  AND is_overridden = 1
  AND ROWNUM = 1
ORDER BY start_date DESC;

This query returns different records on Exadata than on the other server.
On Exadata, it first selects the first row and then does the ordering.
On the other server, it first does the ordering and then returns the first row.

We are using Oracle 11g on both servers.

Now the issue we are facing is that we have upgraded our database servers to Exadata and we have used such queries in a lot of  places in our codebase. So can we overcome this issue?

This answer  is wrong, because the value for rownum is assigned before the order by is executed.

Below is the correct one,

SELECT start_date,
       end_date,
       resort_id,
       ROWNUM
FROM   (SELECT   start_date,
                end_date,
                resort_id
       FROM     employee_activity
       WHERE    employee_id = 27
       AND      start_date < TO_DATE ('2012-11-09', 'YYYY-MM-DD')
       AND      is_overridden = 1
       ORDER BY start_date DESC
      )
WHERE  ROWNUM = 1


Unable to insert into table after creating an After insert trigger I have two tables
Table one: default_sellers_commissions  have:

id
value - INT

Table two:  marketplace_saleperpartner  have:

id
userID
commission - INT default '0.00'
commission_id

what I need is after insert a new row in table marketplace_saleperpartner update the marketplace_saleperpartner.commission value with value from default_sellers_commissions.value where the marketplace_saleperpartner.commission_id = default_sellers_commissions.id

So I create a trigger :

CREATE TRIGGER `marketplacedefaultcommission`
AFTER INSERT ON `marketplace_saleperpartner`
FOR EACH ROW
UPDATE marketplace_saleperpartner
SET `marketplace_saleperpartner`.`commision` = `default_sellers_commissions`.`value`
WHERE `marketplace_saleperpartner`.`comm_id` = `default_sellers_commissions`.`id`
AND `marketplace_saleperpartner`.`commision`='0.00';

P.S:
for some reason I need the  marketplace_saleperpartner.commision to insert for the first time with value='0.00'

now I am getting this error while trying to insert a new row into table marketplace_saleperpartner

MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Any help please ?

Instead of saying marketplace_saleperpartner., say NEW. (or OLD., if appropriate).


Reduce query execution time in frequently updated tables The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets executed 7,00,000 times in a single day with updated data. A query is already optimized and indexed. Table approximately has 300,00,000 rows. Is there any way to reduce the query time?
The table works on the InnoDB engine.

I was planning for few methods please correct me if I am wrong

Memcache the rows using PHP
Use replication Master/Slave architecture in slave have MyISAM (Doubt about the engine in this architecture)
Use MySQL caching mechanism

(a)  query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
(b) buffer_pool
Can both (a) and (b) perform together? Will it improve the performance for such huge data?

Is there some other technique for improving query execution time?

With 3GB of RAM, you should probably keep innodb_buffer_pool_size at no more than 1G.

The Query cache is of minimal use, and it gets slow when you increase its size.  Recommend no more than 32M for query_cache_size.

There is such a thing as "over-normalization"; please provide details.

What indexes do you have?  Do you understand when to use a 'composite' index?

Don't use memcached -- that's just putting a cache in front of a cache.

Replication may be useful if you have lots of users contending for MySQL.  It can speed up a query only if that is a lot of contention during the query.  I don't hear that is the issue.

Please provide the query.