texts: Fastest tool or technique for occasionally syncing Postgres 9.6 databases

texts:
Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Some inputs:

Postgres version is 9.6
DB size is less than 50GB
A full dump and restore takes about one hour. I want to beat this.

The staging db server is used for QA purpose. Whenever new code is merged, the staging app as well as the staging db will be updated. New code is merged several times a day.

One solution I'm thinking about: use something similar to the checkpoint concept.

Initially, restore a full dump to staging server.
Whenever a new code is merged:

2.1 Restore the staging db state to the latest check point
2.2 Get the latest state from production
2.3 Save a new checkpoint

Is it a good direction, and could I use the WAL as the "checkpoint" here?
Thank you.


texts: Wamp which includes mysql,php,apache is only 24.7 MB but the only Mysql is 207 MB here.Why?


texts: mysql index on varchar and date field for improve query speed of select and insert query

texts:
CREATE TABLE IF NOT EXISTS `gsm_smslog220717`
(
    `pdsid` int(11) NOT NULL auto_increment,
    `affiliateid` varchar(20) character set latin1 default NULL,
    `bpushno` longtext character set latin1,
    `psms` varchar(1) character set latin1 default NULL,
    `credits` varchar(5) character set latin1 default NULL,
    `mobileno` longtext character set latin1,
    `dlrid` longtext character set latin1,
    `dr` varchar(50) character set latin1 default NULL,
    `op` varchar(10) character set latin1 default NULL,
    `dtoken` varchar(200) character set latin1 default NULL,
    `sid` varchar(15) character set latin1 default NULL,
    `char` varchar(5) character set latin1 default NULL,
    `gid` varchar(20) character set latin1 default NULL,
    `msg` varchar(700) character set latin1 default NULL,
    `status` varchar(1) character set latin1 default NULL,
    `time` varchar(10) character set latin1 default NULL,
    `subdt` date default NULL,
    PRIMARY KEY  (`pdsid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2444466;

SELECT Query:

select  *
    from  gsm_smslog220717
    where  affiliateid ='hn137754388'
       && subdt = '2017-08-21'
       && status= 's'

How can I improve speed of query? In one second many insert queries are also run on live.

Please help to create best index for this query.


texts: When did a specific database come online?

texts:
How can I determine when the state of a specific database switched to online?

Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats currently is.


texts: How to grant a user UNSAFE ASSEMBLY permission

texts:
How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission?

I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ‘sa’ can't help.

Any idea?

Thank you.


texts:
I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best:

delete jc
from Jtable jc
join J1Table j
on j.JobId = jc.JobId
join nonJobReProcess n
on n.jobid = j.JobId

or:

while exists (select top 1 * from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId
join nonJobReProcess  n  (nolock)
on n.jobid = j.JobId)
begin

delete  top (10000) jc
from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId
join nonJobReProcess n  (nolock)
on n.jobid = j.JobId

end

Please explain the reasons I might choose one over the other, or any alternative methods.


texts: Cannot install SqlLocalDB 2016 on Windows 7 : FormatMessageW failed. Error code returned: 15101

texts:
I downloaded SqlLocalDB.msi (Microsoft SQL Server 2016 LocalDB) using SQLServer2016-SSEI-Expr.exe (version 13.1804.4031.1).

I installed it and whatever I try to do I get the following error:

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>SqlLocalDB.exe v
FormatMessageW failed. Error code returned: 15101

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>SqlLocalDB.exe i MSSQLLocalDB
FormatMessageW failed. Error code returned: 15101
FormatMessageW failed. Error code returned: 15105

The strange this is i could manage to create a new instance, in spite the error

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>SqlLocalDB.exe create "test"
FormatMessageW failed. Error code returned: 15101

But I cannot connect to the original instance MSSQLLocalDB and any operation I try to execute from the command line gives me the same error.

I looked for the error number here but it seems like it has nothing to do with it.

In the instance I created I get the following:

select @@version
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 7 Enterprise 6.1 <X64> (Build 7601: Service Pack 1)

Running on Windows 7 Enterprise SP1


texts: Aggregating nested set models with field constraints

texts:
I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx):
pk  lft rgt idx             value
 1   1  30  2783     20402.710000
 2   2   3  2783    -13885.670000
 3   4   5   131        78.547946
 4   6   7  2783      -586.810000
 5   8   9     1         0.013421
 6  10  11  2783     -1777.630000
 7  12  13   873     10791.237266
 8  14  15  2783       -72.510000
 9  16  17  1697       128.626280
10  18  19  2783      -600.430000
11  20  21  1168         6.949175
12  22  23  2783     -2479.080000
13  24  25  1758       853.502787
14  26  27  2783     -1000.580000
15  28  29  3794      3172.428304
16  31  40  2783       615.940000
17  32  33  2783      -615.940000
18  34  39     1         0.040390
19  35  36     1        -0.000152
20  37  38     1        -0.000300
21  41  42  2783      3012.050000

Since these transactions are nested in a particular way where a child node can be of different nature than its parent (representing a sort of "transformation" along the way), I am having a hard time trying to fetch the "balance" for each parent node in their own nature:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000

How could I achieve this desired output given the original table? I have tried doing something along the lines described in this question, but all I have managed to get was a single parent node and all its children with the same idx, which I could do more easily in my case with SELECT * FROM table WHERE idx = 2783 or similar.
In case it helps, every parent node has a positive value, no node with negative values are allowed to have children.


texts: logging in Bulk logged recovery VS Full recovery model

texts:
I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged fully.But while reading transaction log i am seeing different behaviour,Can please some one shed some light on this.

Database recovery model:

TLOG:

From the above snip we can see even though DB is in Full recovery model,only extent allocations are logged.Is this expected.I am seeing the same behaviour for Bulk logged recovery model too with only change being database pages are also backed up.If only extents are logged in full recovery model,how point in time restore capability is acheived.Please let me know if i am missing any thing


texts: counting the number of a specific word in one SQL column

texts:
I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes.

For this tool vote I'm using PHP and SQL, my database is build like this:

DBNAME=collectifCompost
Table=voteCompost

column1=id PRIMARY AI

column2=user

column3=vote(yes/no/blank)

To build a chart with statistics vote results (% of YES, NO, BLANK..) I want to make a query on my database to count the number of YES NO AND BLANK but I don't know how. Can you help me please?

If you need more information about my code ask me everything.


texts:
I have a server that is churning up the CPU using 60% to 80% constantly. Using a tool from Brent Ozar's First Responder Kit, it shows RING_BUFFER_SCHEDULER_MONITOR is the culprit. What is this, and how can I track down what is doing this?

60%. Ring buffer details: <Record id="3592" type="RING_BUFFER_SCHEDULER_MONITOR" time="215993000"><SchedulerMonitorEvent><SystemHealth><ProcessUtilization>57</ProcessUtilization><SystemIdle>40</SystemIdle><UserModeTime>2748425618</UserModeTime><KernelModeTime>27144174</KernelModeTime><PageFaults>986</PageFaults><WorkingSetDelta>94208</WorkingSetDelta><MemoryUtilization>100</MemoryUtilization></SystemHealth></SchedulerMonitorEvent></Record>


texts: How to store state and analog signal together in DB


texts: Snapshot Agent - Transactional Replication - Why a complete snapshot?

texts: Often, I will add a single article to an existing transactional replication publication. I then start the snapshot agent to copy the new article and it's data over to the subscriber. Most of the time, the agent runs fairly quickly and only generates a snapshot of 1 article (the one that I just added). However, one out of every 4 or 5 times the snapshot agent tries to take a complete snapshot of every replicated article in the entire (very large) database. This causes all sorts of problems due to the locking it creates on the tables. I can't find a rhyme or reason as to why it does this occasionally. Does anyone have any suggestions?


texts: Database connection lost whenever SQL query string is too long

texts:
I recently switched from running my Rails app on a single VM to running the database — MariaDB 10.3 — on a separate (Debian Buster) VM. Now that the database is on a separate server, Rails immediately throws Mysql2::Error::ConnectionError: MySQL server has gone away whenever it tries to make a query where the SQL itself is very long. (They query itself isn't necessarily one that would put significant load on the system.)
An example query that causes the problem looks like this:
SELECT `articles`.`id` FROM `articles` WHERE `articles`.`namespace` = 0 AND `articles`.`wiki_id` = 1 AND `articles`.`title` IN ('Abortion', 'American_Civil_Liberties_Union', 'Auschwitz_concentration_camp', 'Agent_Orange', 'Ahimsa')

... except the array of titles is about 5000 items long, and the full query string is ~158kB.
On the database side, this corresponds to warnings like this:
2021-03-25 15:47:13 10 [Warning] Aborted connection 10 to db: 'dashboard' user: 'outreachdashboard' host: 'programs-and-events-dashboard.globaleducation.eqiad1.wikimed' (Got an error reading communication packets)
The problem seems to be with the network layer, but I can't get to the bottom of it. I've tried adjusting many MariaDB config variables (max_allowed_packet, innodb_log_buffer_size, innodb_log_file_size, innodb_buffer_pool_size) but none of those made a difference. The problem seems to be that the connection is aborted while it is attempting to transmit the long SQL query string from the app server to the database server. (There's no corresponding problem with receiving large query results from the database.)
I've tried adjusting several timeout-related settings as well, although that seems unlikely to be the problem because I can replicate the connection error without any significant wait, just by issuing one of the long-SQL-string queries from a Rails console.
I've tried using tcpdump to see what's coming in, but didn't pick up any additional clues from that.


texts: How is it technically possible for PostgreSQL to keep small tables "in RAM"?

texts:
I used to ask about performance and people would tell me that PostgreSQL keeps small-enough tables entirely in RAM automatically, as to not stress the HDD and thus enable super-fast reading/writing of "small tables", that is, with very few rows which more than easily fit in the available RAM (or what's assigned to PG).
This made sense to me, and made me very happy as I can efficiently communicate between my parallel-running scripts without stressing my poor HDD/SSD and clogging up all the resources.
But then it struck me: How is this possible? How can PG possibly keep a table all in RAM, regardless of how small it is, without losing data integrity?
If it doesn't write to the permanent storage all the time, how can it possibly recover if the power is suddenly cut, or there's a software crash, or some other disaster happens while it's running? It doesn't add up to me the more I think about it.
The only answer I can think of is: No, it doesn't actually do this. I don't see any possible way that PG could avoid losing data if it never (or rarely) writes the data to the permanent storage.
If this is possible, but only after enabling some kind of flag for the table, or setting, I'd like to know about that flag/setting. I'd like to enable this for tables which only contain "internal communication" data of the kind which has no value besides as a "common data storage" for actively running scripts.


texts: Store values in select as a temp variable and reuse them

texts:
I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically what I am doing with the query is comparing a DateTime field against the current time to see if the date is in the past or the future. Maybe I have over complicated something simple but from looking around on the internet this seems to be the only way to compare a DateTime field.

I want to be able to reuse the following repetitive parts of the query because I have to list them twice to get the floor value:

(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)

AND

(CAST(GETDATE() AS FLOAT)

Here is the query:

    SELECT
          bq.quoteID
        , bq.unsentNumber
        , bq.sendSubsequent
        , bq.unsentDelay
        , sr.contractorID
        , CASE
            WHEN (CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)  - FLOOR(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT))) <= (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)))
                THEN 1
                ELSE 0
            END AS 'Add Bid'
        , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
        , GETDATE() AS 'Time Now'
        , e.email
        , e.name
    FROM tblCustomerBidQueue bq
    INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
    INNER JOIN tblContractors c ON c.ID = sr.contractorID
    INNER JOIN tblEmails e ON e.custom = bq.QuoteID
    WHERE bq.status = 'Queued'

Thanks in advance for any help!


texts: SSIS - dynamically merging a group of files

texts:
I'm trying to create an import package for our new mssql database and I'm having to merge a group of 20 files together to be fed into a table on the database. However not all of those 20 files will be there every time the import process is run.

However SSIS expects a file to exist so it throws an error. While you can get around the task via a "foreach loop" it only means that the task doesn't run and no merging takes place.

Is there any way around this problem or do I have to do a new task each time I need to merge a new file as that seem kind of unnecessary?


texts: SQL Server : Tracking a slow running query on completion

texts: Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, 1 hour. Till Q1 is running i.e. executing, I can track it using sys.dm_exec_requests DMV. If I understood it right, once Q1 is completed, its statistics will be no more available in this DMV. Instead, we need to look at sys.dm_exec_query_stats. But how do I get the statistics of that specific query Q1 which had been slow-running ? Meaning how do I relate the data I earlier got in sys.dm_exec_requests and sys.dm_exec_query_stats?


texts: Why does adding an index on a column containing a char version of a UUID decrease performance?

texts:
I have a table with 3.5 million rows containing UUIDs in their ascii representation (so 36 chars) and an integer amount.
I need to SUM all amounts grouping by UUID, so the query is very simple:
SELECT uuid, SUM(amount) FROM table GROUP BY uuid;

results:

with an index on uuid: Time: 68.074s
without an index: Time: 8.188s

execution plan with index:
| id | select_type | table  | type  | possible_keys    | key      | key_len | ref    | rows    | Extra  |
+----+-------------+-----------------------------+-------+------------------+------------------+---------+
| 1  | SIMPLE      | amount | index | uuid_idx         | uuid_idx | 110     | <null> | 3424833 | <null> |
+----+-------------+-----------------------------+-------+------------------+------------------+---------+

execution plan without index:
| id | select_type | table  | type | possible_keys | key    | key_len | ref    | rows    | Extra                           |
+----+-------------+-----------------------------+------+---------------+--------+---------+--------+---------+-------------
| 1  | SIMPLE      | amount | ALL  | <null>        | <null> | <null>  | <null> | 3424833 | Using temporary; Using filesort |
+----+-------------+-----------------------------+------+---------------+--------+---------+--------+---------+-------------

Am I missing something or does an index on that column really dramatically decrease performance on that kind of query?


texts: As per title, how to set Oracle SQL Developer default linesize so that I don't have to set it every time I open the program?


texts: In Postgres - when transaction IDs wraparound what are their numbers?

texts:
In Postgres 11, when transaction IDs pass the 4 billion wraparound mark, assuming that the database is properly autovacuumed, what will be the next txid returned by txid_current()?

Will the returned transaction IDs start again from 1 or will they continue to have IDs greater than 4 billion and the txid_current() will return numbers greater than 4 billion.

The documentation says "... these functions export a 64-bit format that is extended with an “epoch” counter so it will not wrap around during the life of an installation."  So my understanding is that txid_current() returns bigint and the the returned txid will not start from zero. postgresql.org/docs/11/functions-info.html


texts: SQL Server performance issue (Log Shipping became slow)

texts:
I'm facing the problem that MS SQL Server 2016 suddenly become slow.
For example, restore log process (log shipping). I can see LASTWAITTYPE is PAGEIOLATCH_EX in SYSPROCESSES table during the process.

At the same time on disk where db is situated I can see high value of Avg. Disk Sec/Read performance counter (sometimes 400, sometime 3000 during restore log process)

DBA says it's normal IO fluctuating and I do not know what values was before I faced a problem.
May be the other reason of slow performance than IO subsystem?
I also noticed that all processes, queries, SSIS become slow.
Here is wait stats, I can see many CXPACKET. Cost threshold for parrallelism is 50, Max degree of parrallelism is 4.

Could you advise me please where to search problem? Thanks.


texts:
I am getting java.sql.SQLException: ORA-01410: invalid ROWID for some of the rows.

Here is my query.

"select OP.PLU_LONG PROD_ID," +
                          "OP.NEW_PRICE PRICE," +
                          "OP.REG_PRICE REGPRICE," +
                          "OP.QUANTITY_LIMIT QUANTITY_LIMIT," +
                          "OP.ON_AD_DATE AD_START_DATE,"+
                          "OP.OF_AD_DATE AD_END_DATE " +
                          "from ABC OP";

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(dbUrl,dbUser,dbPw);
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sqlSTGOP);
            // System.out.println("Entering while block");
            while (rset.next()) {
                String price = rset.getString("PRICE");
                String reg_price = rset.getString("REGPRICE");
                String prod_id = rset.getString("PROD_ID");
                int quantity_limit = rset.getInt("QUANTITY_LIMIT");
                Date ad_start_date = rset.getDate("AD_START_DATE");
                Date ad_end_date = rset.getDate("AD_END_DATE");
            }
        } catch (SQLException sqe) {
            logger.error("**** SQLException occured while getting Special Price and Quantity Limit:"+sqe);
            error2 = sqe.toString();
            System.out.println("Problem in creating result set" + error2);
        } catch (Exception e) {
            logger.error("**** Exception occured while getting Special Price and Quantity Limit:"+e);
            error2 = e.toString();
            System.out.println("Problem in creating result set" + error2);
        }

The error happened here only. When we restart the process, it goes away. It happens once in a while. When we get the values from resultset, then it throws an invalid rowid sqlexception.

sqlSTGOP is a string constant refering to above select query.
driver: oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@a.b.c.d:1521:db
java version: jdk1.6
version of jdbc: ojdbc14_g.jar
dbUrl is the url of database which has this ABC table
dbPw is the password of that database
oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

There is no PK or index on this table.

  Is someone deleting rows?

There is one process which runs before this process which removes and loads the data under table ABC and when it finishes, then this process runs after that. That is why we are unable to find out, why this error is coming even the earlier process finishes its task before starting of this process.

No other process, procedure, trigger, package is using this table ABC.


texts: Check if any of the values is in a subquery result

texts:
I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one of two fields). I could just do stuff like this:

 select *
 from Customers
 where orderId in (select...)
 or secondaryOrderId in (select ...)

The problem is that the subquery is enormous, both in time it takes to execute, and in the screen space it takes. Is there a way to check if one of the fields contains one of the desired results?


texts: Check whether empty strings are present in character-type columns

texts:
I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to write a CHECK on every table that has a VARCHAR, TEXT (or similar) column.

Assuming that one can't or does not want to do that is there a way to write a simple, generic, SQL query (obtaining table and column names from the database's metadata) that would check if any textual columns in the database contain empty strings?


texts: Where can I find scenarios to work through T-SQL

texts: I've been working through the exam guides for the new 70-461 MCSE data exams and i'm at a point where I want to practice my T-SQL.  Does anyone know of any good resources whereby scenarios are posed and you have to create a solution through practice.  For example, when I learned C# I would work towards a goal of creating a certain type of system or project like a web app or a console app that did a certain thing.  I'm struggling to come up with test scenarios to improve my T-SQL experience because I don't have a frame of reference for what professionals in SQL do in their day jobs.


texts: Is there a way to minimally log insert under Always on?

texts:
I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model?

I have a process that is executed each day and insert few millions of records in a table. While the operations continued the transaction log file size is increased dramatically ( from 1 GB to 40 GB).

As I have read I can used some variations of INSERT which are not fully logging the operation but I am concern about the effect of switching the recovery model?


texts: How to write join query in sub query method?

texts:
This is the question:
What is the most common stackoverflow tag_type? What companies have a tag of that type?
My Solution:
SELECT type, count(tag) AS count
  FROM tag_type
group by type
order by count desc;

Result of this query is:
type       count
cloud       31
database     6

And so on but I selected cloud in the type column as mentioned below and got my result which is correct.
SELECT tag_company.tag, company.name, tag_type.type
  FROM company
       -- Join to the tag_company table
      INNER JOIN tag_company
      ON company.id = tag_company.company_id
      -- Join to the tag_type table
      INNER JOIN tag_type
      ON tag_company.tag = tag_type.tag
  -- Filter to most common type
  WHERE type='cloud';

My Question:
I have got my desired result though but I want to eliminate the manual work by simply typing (type =cloud) and looking for dynamic way of solving this query. How can I do that?
And how can this query be solved with sub query method? Please help.
Or how could I combine these steps in a single query by using a subquery in the WHERE clause instead of the value 'cloud'?


texts: How to join tables when one of them doesn't have any relationship with the others

texts:
First of all, I know what I am about to show you is a completely DB mess but I just got it like this.

Tables :

Authors

manufacturers_id manufacturers_name
1                 Name1
2                 Name2
3                 Name3

Authors_to_manufacturers (useless table since there is no authors_id)

authors_id  manufacturers_id
1              100
2              200

manufacturers

manufacturers_id   manufacturers_name
 1                     Name1
 2                     Name2
 3                     Name2
 ...100                Name1,Name2
 101                   Name1,Name2,Name3
 102                    NameX

products

  products_id  products_name manufacturers_id (This is the PK from manufacturers, not authors)
    1         product1         100
    2         product2         300
    3         product3          1

The diference between authors and manufacturers is that authors table are "single" authors while manufacturers can be more than 1 authors together. Also manufacturers table contains all the exactly info of authors but with more rows (because some books have more than 1 author).

Authors table is screwed, since the primary key is the same as the manufacturers (can't change it now, it will mean a lot more of work that I am not authorized to do) and authors_to_manufacturers (It was suppose to be the connection for both tables, but whoever made this put authors_id when there is none as you could see).

Re-formulating my question:

How to make a query than join tables when one of the tables doesn't have any relation with the others (also the primary key is the same as one of the others tables)

This is what i got so far

SELECT count(products_id), authors.manufacturers_name
FROM products
INNER JOIN authors ON authors.manufacturers_id = products.manufacturers_id
WHERE authors.manufacturers_name = (select manufacturers_name from manufacturers) group by manufacturers_name;

of course this doesn't work because "subquery has more than one row", but thats exactly what i want (like if it would be ("...where manufacturers_name = manufacturers.name..."), so it would match all the manufacturers name from one table that match with the other table.


texts: Struggling to design addresses table for fast querying

texts:
I have two tables: addresses and locations.

Locations
zipcode
city
county
state

Addresses
line1
line2
city
county
state
zipcode

Posts
Id
Address_id
Body

All my users are required to provide at least a personal address and some need to provide the business address.

My users can create two types of posts: first requires to have a full address and the second requires just the location (state, city, zipcode).

Now, the issue comes because I need to allow searches for posts based on location. The search is always based on city state and zipcode (not street numbers).

Does it make sense to place all the addresses inside the Addresses table and for the posts that need just the state, city and zipcode to leave the other fields blank? Would this slow down posts searches based on addresses?

Would having in my DB 5.000-10.000 users and maybe 200.000 addresses really slow down the process of searching for first type of posts based on the address?


texts: Error wile debugging (signature buffer too small)

texts:
I'm trying to debug a package in SQL Developer, but I keep getting error:

ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [pbjrms - signature buffer too small], [38], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [pbjrms - signature buffer too small]
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Closed Connection

Package compiled without errors. Anyone had this problem before?


texts:
I've used Brew to install PostgreSQL.  I want to log in with psql. Since I haven't set a password I 'm trying to login without one.  When I try I get this:

psql: fe_sendauth: no password supplied

I have this in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

Brew is a clean install.  I believe I've removed all instances of postgres (psql doesn't run if I remove the brewed postgresql).

What am am I missing?


texts: How to use GENERATED from SELECT with JOIN?

texts:
This is my database. FoodItems is just normal table but I want to use virtual columns to generate all the related data, e.g. Servings.kcal is equal to related (FieldItem.kcal*unit)/100 (the data is specified in kilocalories per 100g/100ml):

CREATE TABLE IF NOT EXISTS `DietDB`.`Servings` (
      `id` INT UNSIGNED NOT NULL,
      `FoodItem_id` INT UNSIGNED NOT NULL,
      `name` VARCHAR(255) NOT NULL,
      `units` DOUBLE UNSIGNED NULL,
      `kcal` INT GENERATED ALWAYS AS (SELECT (FoodItems.kcal*units)/100 FROM Servings JOIN FoodItems ON Servings.FoodItem_id = FoodItems.id) VIRTUAL,
      `carbohydrate` DOUBLE UNSIGNED NULL,
      `carbohydrate_sugar` DOUBLE UNSIGNED NULL,
      `fat` DOUBLE UNSIGNED NULL,
      `fat_saturated` DOUBLE UNSIGNED NULL,
      `fat_trans` DOUBLE UNSIGNED NULL,
      `protein` DOUBLE UNSIGNED NULL,
      `fibre` DOUBLE UNSIGNED NULL,
      PRIMARY KEY (`id`, `FoodItem_id`),
      INDEX `fk_Servings_FoodItems1_idx` (`FoodItem_id` ASC),
      UNIQUE INDEX `name_UNIQUE` (`name` ASC),
      CONSTRAINT `fk_Servings_FoodItems1`
        FOREIGN KEY (`FoodItem_id`)
        REFERENCES `DietDB`.`FoodItems` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

This is obviously wrong as the query returns the whole table, not single value and it yells an error... but it presents what I'm trying to achieve there. How could it be done?


texts:
Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with information and I'm trying to devise a means of efficiently working with a row subset defined as newly added rows, but I need that date meta information to make said differentiation possible. Is this at all possible?

A dirty solution, that sadly only works on some tables, I've thought up includes keeping track of the number of rows I've collected (this works because the primary key is generated by the database) and then making queries with an offset. If the date added meta information exists then I can devise a generic solution that will suite all of my tables.

Please note that I can't add an explicit data added column because I already have a ton of rows collected over several years.


texts: What does MBRContains and MBRWithin test? What does "contains" and "within" mean?

texts:
MBRContains(g1, g2) is defined as,

  Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 contains the minimum bounding rectangle of g2. This tests the opposite relationship as MBRWithin().

MBRWithin(g1, g2) is defined as,

  Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 is within the minimum bounding rectangle of g2. This tests the opposite relationship as MBRContains().

What exactly does contains and within mean in that context?


texts: Can a Database request automatically remove itself from the queue after a delay?

texts:
I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again.  If many instances are run and requests are made simultaneously, the table locks will be queue, like updates or other table modifications would be.

In a positive spirit of bug prevention, I'm concerned that if the Database crashes or suddenly becomes unavailable for any reason, while the table is locked, the program instances in queue would be stuck there, waiting for an answer that will never come.

Obviously, getting my program frozen is undesirable. Is there any way a request can remove itself from the queue after a set time?  It there maybe a better way to do this?

If this proves to be a problem, I do know that I can use the NO WAIT parameter in my table lock, and then repeat requests for a certain time before giving up, but that seems quite inefficient and I lose the benefit of the database queue.

What do you think?


texts: Is it worth the time to review indexes suggested by index tuning advisers?

texts:
I find I am spending a lot of time lately examining lists of proposed indexes that were generated by an index tuning adviser or some other automated process.

Back in the day (circa 2005) I found the suggestions to be rather awful in that they seemed geared to one specific query and more often than not were merely a covering index that had every column in the table whether or not those colums were already indexes.

As time has progressed, I feel the index tuning advisers have improved dramatically, but I still have a built-in mistrust and still want to spend the time to review each one.

I am trying not to be pessimistic, but it is difficult when a vendor (Microsoft) suggests to add multiple overlapping covering indexes to improve performance, especially when we are paying for storage space.  I also don't want to spend my own time needlessly.  I would rather be fixing poorly written queries.


texts:
Currently i have an azure subscription in it has a server and a database.

How could i encrypt an SQL azure table and when the encryption is done i can still insert some data on the table?

I already tried the encryption part but when i am trying to insert a data i received couples of error and i can't insert the data.

I am a newbie when it comes to azure and as a DBA


texts: SQL Server 2008R2 - BAK file backup performance issue

texts:
Just note I'm not a DBA or anything close to that.

Problem:

We are using SQL Server 2008 R2. We have 32 databases and backup to local disk (3PAR) using SQL Server maintenance plan. Normally it takes 8 hours for 950 GB. few days ago backup took over 25 hours with transfer rate of 25 MB/Sec. Normally it is minimum 130 MB/Sec. Now I'm trying to troubleshot if is is a IO issue or something wrong with the DB server or the databases.

I have done some manual backup testing on a 52 GB databases and the test results are mix.

Backup start to finish as normal with average transfer rate 150 MB/Sec (total 8 mins)
Backup is really quick in start and then slows down, first 20 % of the backup with 250MB/sec then can slow down to 25 MB/Sec (35 Mins)
Backup start slow with 25mb/sec (35 Minx)

Troubleshooting steps that I have done

Server environment

No Antivirus or 3rd party tool running in the background
No updates done on the OS

Disk

No changes with the Database F disk (2TB with 240 GB free)
No Changes with the backup Z disk (2TB)

SQL server

No changes

Ran those following SQL command during the slow backup performance

Command 1

Select session_id,command, percent_complete
From sys.dm_exec_requests

Finding: no fault found

Command 2

SELECT * FROM

sys.dm_os_wait_stats

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

Finding: no fault find

Command 3

select * from sys.dm_exec_requests

Finding:

Noticed BACKUPTHREAD wait time keep increasing and can get up to 3245800

Result is link below

https://www.dropbox.com/s/8nnsch0msr6mi54/output.xlsx?dl=0


texts: How does LEFT JOIN with WHERE clause works?

texts:
Given the query below

    SELECT * FROM tableA
    LEFT JOIN tableB ON tableB.id = tableA.id
    WHERE tableA.name = 'e'

.

tableA                        tableB
-----------                   -----------
id    name                     id    school
-----------                   -----------
1     a                         1     AA
2     b                         2     BB
3     c                         3     CC
4     d                         4     DD
5     e                         5     EE

.

What baffles me is the process that is happening behind.

Does the system JOINs first both tableA and tableB before they select only the row from the WHERE clause?

If yes, then what about this query

SELECT * FROM
(
    SELECT * FROM tableA
    WHERE name = 'e'

) A LEFT JOIN tableB ON tableB.id = A.id

Does this query select first using the WHERE clause before it JOINs the other table?

Sorry I can't find the answer on the web. I wanted to lessen the MySQL resource bandwidth. Does the second query is the best for this situation?


texts:
Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I've seen a WHERE 1 clause being used in many places, even where other conditions are not present.

Why is this done?
Are there specific benefits to the execution time?
Does it enable other functionalities?
Is using WHERE 1=1 similar to this?


texts:
I have data as following(it is just an sample, I have 100+ different types.):

advertiser_id activity_type counts
1               1            2
1               2            5
2               1            3
2               3            9

I would like to change it into

advertiser_id activity_type1 activity_type2 activity_type3
1              2                 5           0
2              3                 0           9

I wrote code as

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`activity_type` = ', activity_type, ',counts,NULL)) AS counts', activity_type)) INTO @sql FROM ceciliadb.tbltmp_act
;

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But there is error as

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id' at line 1

Is there anyone can help?


texts: Partition constraint not used for joins involving tables partitioned by timestamp

texts:
I have a partitioned table structure like:

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

And so on. Each table has approximately 20M rows.

If I query for a sample of sensors and a sample of timestamps in the WHERE clause, the query plan shows the correct tables being selected and indexes being usedm e.g.:

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00'
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00'
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

However, if I use a CTE, or put the timestamp values into a table (not shown, even with indexes on the temporary table).

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

Something like the below

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

Performs an index scan on every table. Which is still relatively fast, but with increasing complexity of queries, this can turn into seq scans which will end up being very slow for retrieving ~40K rows from a limited subset of partitioned tables (4-5 of 50).

I'm concerned that something like this is the problem.

  For non-trivial expressions you have to repeat the more or less verbatim condition in queries to make the Postgres query planner understand it can rely on the CHECK constraint. Even if it seems redundant!

How can I improve partitioning and query structure to reduce the likelihood of running seq scans on all my data?


texts: Why does this MySQL date_sub statement return wrong values?

texts:
I had a complex statement that was returning strange values and I traced it to the following line:

SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE)
ORDER BY updated;

It returns in my case:

|  ID      |  updated              |
|  123173  |  2014-06-24 13:14:10  |
|  123272  |  2014-06-24 13:17:24  |
|  123273  |  2014-06-24 13:29:13  |
|  123153  |  2014-06-24 15:35:54  |
|  123231  |  2014-06-24 15:35:55  |
< Snipped a bunch of entries after 15:35:55 >

Interestingly, changing to INTERVAL 30 SECOND yields no differences, but INTERVAL 24 HOUR does add a few more entries.

For the record, we're using MySQL 5.1.48, and updated is type DATETIME.  The data after 15:35:54 was the expected result.

My question is what is causing this to happen, and how can I work around it?

UPDATE:
I have identified my own problem, and it's not a problem with MySQL.  It's a time zone problem.

The above listed entries are UTC, as that's what our application works in.  However, the actual server/system time is our local time.  The correct way of executing the code would be:

SELECT id, updated FROM status WHERE updated > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 6 MINUTE)
ORDER BY updated;


texts: Kill concurrent materialized view refresh PID

texts:
Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them.

Is there some other way to obtain the PID, in order to perform pg_terminate_backend(pid)?


texts: Unable to check is schema is present first

texts:
I've created a SQL  script that will release a process to numerous SQL  Server instances so I want to check if a paticular schema is present before creating the schema.

I'm using the below and it creates an error but I don't understand why?

IF NOT EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'SQLInstance_Baseline')
    CREATE SCHEMA [SQLInstance_Baseline];

If I wrap the CREATE SCHEMA [SQLInstance_Baseline] in an EXEC i.e. EXEC('CREATE SCHEMA [SQLInstance_Baseline]') it works fine, but I don't really like doing this for best practice reasons.


texts:
I have a mysql query

select sum(duration),app_name,app_id,user_id
from timesheet
where user_id=164
group by app_id
order by sum(duration) desc
limit 5;

The result is

sum(duration), app_name,       app_id,  user_id
'626919371',   'Idle.exe',     '0',     '164'
'38220511',    'LockApp.exe',  '2204',  '164'
'36675000',    '',             '1',     '164'
'27713000',    'LockApp.exe',  '8148',  '164'
'16698661',    'chrome.exe',   '8548',  '164'

However I want the top 5 app for every user_id instead of just 164. Can you pelase guide me as to how this can be achieved in a single query. Or do I need to fire it for every individual user


texts: How to truncate all tables with a specific name in all databases

texts:
I want to delete all tables named error_log in all of my databases that have such a table.

truncate table error_log

I am using SQL Server 2000.


texts: Is the Execution Path for a Function invoked inside a Stored Procedure cached?

texts:
According to some of my research a benefit of using Stored Procedures over Functions is that they cache an execution path which provides performance benefit due to not having to recalculate it.

So I was wondering if I invoke a Function from within a Stored Procedure is the entire Stored Procedure still cached?

Here's a mocked up example function - it would be used to add further details onto a table selected in the Stored Procedure.

CREATE FUNCTION [dbo].[fnExampleFunction]
(@EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS @retEmployees TABLE
(AppraisalLevelId INT,
 JobTitleId  INT,
 LocationId INT,
 Name1 nvarchar(200),
 Name2 nvarchar(200),
 Name3 nvarchar(200)
)
AS
     BEGIN
         INSERT INTO @retEmployees
                SELECT
             ed.*,
              jt.JobTitleName,
              l.LocationName,
              al.AppraisalLevelName
                FROM @EmployeeDetails ed
                     LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
                     LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
                     LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
                     LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId;
         RETURN;
     END;
GO

So the Stored Procedure in psuedo would be used IF @Condition Return SelectedTable ELSE Return fnExampleFunction(SelectedTable)


texts: Case-insensitive collation still comparing case-sensitive

texts:
I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The SELECT statements used by this program can not be altered.

The abstract problem is that we somehow need this search to be case insensitive but it currently is case sensitive.

I read that Postgres 12 does support non-deterministic collations which allow for this behavior.

I have a Postgres Server (Version PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit) installed on a German Windows machine.

So for testing purposes, I created a new database to test on:

CREATE DATABASE collation_test
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

In this database I created the following collation I found in an article about these collations

CREATE COLLATION collat_ci (
  provider = 'icu',
  locale = 'und-u-ks-level2',
  deterministic = false
);

After this, I needed a table to test this collation with

CREATE TABLE public.person
(
    "Id" bigint NOT NULL,
    "Name" text COLLATE public.collat_ci,
    PRIMARY KEY ("Id")
);

ALTER TABLE public.person
    OWNER to postgres;

INSERT INTO person VALUES
(1, 'Robin'),
(2, 'robin');

So now I tried the following select query:

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1
)
= 'Robin';

Which returned true as expected as the text in the database and the literal given match completely.

But if I try the same with a lower case r which I would expect to match because of my collation but it still returns false.

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1
)
= 'robin';

When trying to compare both inserted rows' name to each other I still got false as a result:

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1 --'Robin'
)
=
(
    SELECT "Name" FROM person p2 WHERE p2."Id" = 2 --'robin'
);

Does anyone know why my comparison does not behave as expected and how to get it to do so?


texts: Why is my SQL server using ~4 GBs of memory?

texts:
I notice that pretty often my SQL server will eat up 50% of my 8GB of memory. This usually happens after I finish running some SQL queries, not while they're running. The most common query is one that is generating millions of rows of random data. In the query I use

BEGIN TRAN
  ....
COMMIT TRAN

I'm guessing this might be the cause, but I don't know nearly enough about SQL to figure out the issue.


texts: SQL Server native log backup running every 5 minutes

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


texts: INDEX REBUILD IMPACT ON sys.dm_db_index_usage_stats

texts:
In which case (or sql versions) are the index statistics reset by index rebuild?
There are a lot of articles which makes me think that perhaps this behavior wasn’t always the case.


texts: Block SSRS Developers from embedding SQL Statements in Report

texts:
Is there any way I can automatically block SSRS reports with embedded SQL from accessing my Databases?

I want to force a standard practice of using Stored Procedures, so the DBAs can take back control.

I’d like to avoid implementing a Policing solution where daily jobs interrogate the SSRS Reports for SQL Statements.

I want to block users from having Read Permissions on the databases, so here’s my plan:

All Data Sources use an SSRS service account that has read
permissions on designated databases.
The SSRS service account will be given execute permissions on a per
SP basis (so I can audit every SP that is created to support an SSRS
report).
The DBA will be in control of the SP deployment and permissions
management.
Access to the SSRS rdl file is managed via AD, this is the single
area where I manage end user permissions. (the user can only see data
via an SSRS report)

My problem is that a “Rogue” Developer can modify a report to contain a SQL statement and use the SSRS service account to  read data.

If I can’t Block this behaviour then the very busy DBA will have to take ownership of deploying SSRS reports to live.


texts: Making collations uniform across multiple SQL Server 2008 databases

texts:
I have been assigned to try and make collations uniform across databases. Currently, it's a mess, different collations everywhere.

This is mostly solved by using collate database default in queries and stored procedures.

Bit of a broad question, but what would be a good approach for making it all uniform. Has anyone got tips / or a T-SQL script?


texts: Distributing MySQL database on n systems

texts:
I have a webserver in my application which talks to a single MySQL node.  Instead of having just one MySQL node, I want to have a cluster of n nodes which form my database.

Is it possible to do this?  Can some one point to a resource where I can read about how to do this.

I read about MySQL cluster but am more confused now; is there any beginner tutorial for that?

I don't want to rewrite the application, as I am under the impression that application code should never know whether it's talking to one DB node or a cluster of DB Nodes.


texts:
I am discussing a controversial subject on the server Collation.

When I prepare the 70-462 exam I learned that if we want to change the default collation of the new coming databases we have to change the collation property in the 'Model' database.

However on the internet all articles said that The collation of system databases cannot be changed. And I validate this when I tried to execute the following query:

Alter DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS

I get an error telling that it is impossible to change the collation of the database Model because it is a system database.

Is it true? and if yes, is there any way to change the default collation of the server without doing a reinstallation and by t-sql code?

Thanks


texts:
I am experiencing a weird behavior with SSIS parameters.

At the start of the SSIS package, I have a script task. In the Script Task Editor I select a package parameter as a read only variable.

When I execute the package, Visual Studio crashes.

Other Details:

Visual Studio 2015
Target Server Version is SQL Server 2012
Execute Script by itself will work
SQL Server Data Tools - 14.0.60923.0
Two data flows and an email task after the Script Task
Disable data flows and it works without crashing VS

Is there a problem using package parameters in this manner?

It crashes shortly after I right click and select Execute Package. No break points set.


texts: Using a join to return multiple values of the same column?

texts:
I have two tables

user
Rank

In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table.

Since I have to select both rank_id and supervisor_id from same table(Rank), how do I join both tables to get the result with name, rank name and supervisor?

DDL

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `Name` varchar(90) NOT NULL,
  `rank_id` int(4) NOT NULL,
  `supervisor_id` int(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `Name`, `rank_id`, `supervisor_id`) VALUES
(1, 'john', 3, 2),
(2, 'james', 5, 1),
(3, 'geore', 4, 3),
(4, 'aby', 3, 2),
(5, 'john', 2, 1);

CREATE TABLE IF NOT EXISTS `rank` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `rank_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `rank`
--

INSERT INTO `rank` (`id`, `rank_name`) VALUES
(1, 'president'),
(2, 'vice president'),
(3, 'prime minister'),
(4, 'cabinet minister'),
(5, 'minister');

expected result

I need result like below mentioned table

CREATE TABLE IF NOT EXISTS `result` (
  `id` int(7) NOT NULL,
  `name` varchar(90) NOT NULL,
  `rank` varchar(50) NOT NULL,
  `supervisor` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `result`
--

INSERT INTO `result` (`id`, `name`, `rank`, `supervisor`) VALUES
(4, 'aby', 'prime minister', 'vice president');


texts: Missing FROM-clause in RETURN QUERY EXECUTE FORMAT loop

texts:
This query works fine, but I need to execute it for multiple tables:
select 'service:dms_fdo_query:joined' as metric_name, hypertable_size('prom_data."service:dms_fdo_query:joined"') as raw_size

I wrote this function:
CREATE OR REPLACE FUNCTION public.get_metric_sizes()
  RETURNS TABLE(metric_name text, raw_size bigint) AS
$func$
DECLARE
   metric_table name;
BEGIN
   FOR metric_table IN SELECT table_name FROM prom_info.metric
   loop
    RETURN QUERY EXECUTE FORMAT('SELECT %L as metric_name, hypertable_size(prom_data.%I) as raw_size', metric_table, metric_table);
   END LOOP;
END
$func$  LANGUAGE plpgsql;

but I am getting this error:
SQL Error [42P01]: ERROR: missing FROM-clause entry for table "prom_data"
  Where: PL/pgSQL function get_metric_sizes() line 7 at RETURN QUERY

I have really no idea how to fix this, any suggestion would be greatly appreciated!


texts: SQL Query: Selecting a person who is a member of all existing groups

texts:
I'm tinkering around with my very first SQL queries and am completely baffled by a problem I encountered.

Let's say I have the following database:

PERSON(PID, Name)
MEMBER(PID, GroupID)

How would I go about creating an SQL query that selects only the tuples of the persons who are a member of all existing groups (i.e. GroupIDs)?

I tripped over everything I could find regarding IN, EXISTS, ALL and the likes without finding an elegant solution.

Thank you very much,

Pety


texts: Java Spring application Database triggers auditing - how to provide the right user that made the change

texts:
We are developing a new web application.
one of the most basic requirement is to audit all entities changes into a separate table.

We would like to use DB triggers for that purpose.

We use MySQL as our RDMBS.

The problem we now foresee is that whenever a trigger is pulled, and insert a new entry for the DB, it cant possibly know the (applicative) user that made the change. (all users have different ids, but spring uses a single user account for the db manipulations.)

Any ideas how to resolve this issue?


texts: Transactional Replication Snapshots causing serious locking after 2012 upgrade

texts:
I am experiencing serious locking when adding new articles to an existing transactional replication publication, which is hanging the website. This is the first time I have attempted this since I recreated the replication without issues after my upgrade to SQL 2012.

This is a process I have completed when on sql 2008r2 dozens of times when the publisher database was much busier, so don't think this is traffic related. I can see from the replication monitor it only seems to be selecting those specific tables, its hard to tell what is causing the blocking with sp_who2 as the whole site gets jammed with hundreds of blocks so I just have to kill it as cant take the site down to troubleshoot.

I run this to make sure it only captures the new articles

EXEC sp_changepublication
@publication = 'statics',
@property = N'immediate_sync',
@value = 'FALSE'
GO

I am trying to add 3 lookup tables, only a few rows each and currently not in use by the system, so this leads me to believe that its locking all the articles in this publication which would indeed lock the website. am I missing some new setting in SQL 2012?

for reference I am adding new articles with the following code -

sp_addarticle @publication='statics', @article='SupplierCarrierLink_tbl', @source_object='SupplierCarrierLink_tbl'
GO

EXEC sp_addsubscription
@publication = 'Statics',
@article = 'SupplierCarrierLink_tbl',
@subscriber = 'CHICAGO',
@destination_db = 'MLT-Replicated',
@reserved='Internal'
GO

it worked fine in my test and pre-production systems but obviously they don't really have any serious load. The only other difference I can think of is that my production is still running in 2008 compatibility mode, could this make a difference? or can anyone suggest why this behaviour has now changed ?


texts: Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

texts:
Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.

First, I do this:

select max(ora_rowscn) from tablename;

I take note of the number.  Then I do an insert, update, and a delete, check that max value before and after each.  It appears to increment for each type of change.

If you're wondering why I am doing this, we cache a list of entities in our C# windows service.  This service runs on two load-balanced servers, so there's a separate instance of each running.  When an update occurs on server A, server B needs to know about it.  What I want to do is cache max(ora_rowscn) into a variable.  Every time our application goes to insert, update, or delete a record, it will get a new max from the database.  If the value is different then it obviously knows it needs to go get a new list from the database.

So my actual question is this:  Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value?

Edit: Can someone add ora_rowscn as a tag?


texts: When did sp_OACreate et. al. become deprecated?

texts: I had an issue on an instance of SQL Server I'm supporting and many of the Stack sites have information on these features, but most of them basically say "Don't use those; they are deprecated/buggy/leaky".  I'm going to try to implement work arounds for my specific issue, but my question is when did features such as sp_OACreate and sp_OAMethod become deprecated?  I have to explain this to my Sys Admin and boss in order to justify a rewrite and I haven't come across much just by Googling (although perhaps my search terms are poor).


texts: MSSQL Linux Crashes with data files on EXT4 formated ZVOL Reason: 0x00000003

texts:
Reason: 0x00000003
      Message: 0 == (m_PhysicalSectorSize % m_LogicalSectorSize)
   Stacktrace: 0000558d9d8c90c1 0000558d9d91ff08 0000558d9d8cd02c
               0000558d9d8d0965 0000558d9d8cdc3b 0000558d9d8cd4d2
               0000558d9d8ce20f 0000558d9d8c2c17 0000558d9d8c1e66
      Process: 300 - sqlservr
       Thread: 471 (application thread 0x1158)
  Instance Id: af6ea342-baec-44de-a791-9c7c9537116a
     Crash Id: 94efb159-ed5c-4247-b180-a5b3a4cceb29
  Build stamp: d250a9f0724e59ff452e464a4ab43d5d070a14aef28f4ce3a48a2d785986baa6

Wanted to make use of ZFS over your run-of-the-mill disk to get all the benefits it offers. However it's a known issue that this isn't supported in such that you can't simply place the data files on a mounted pool. So I tried to use ZFS's ZVOL option to give me a traditional partition backed by ZFS, copied the database files over (after shutting down the server), remounted the new partition in the same place as the old one, and attempted to start to get the error shown above.

I'm semi-sure it's because of mssql not supporting ZFS but I wanted to post this here if anyone has any input on what might be happening.

At the moment I'm making due with a file block device located on the pool and dealing with the inherit chicken-egg problem whenever it happens.


texts: Creating a MySQL db from an Informix database

texts:
There is an existing database that was created in Informix. I would like to export it and then import it into MySQL.

I tried to export it using dbschema and it created the dbase.sql file and several table00123.unl files for the tables.

I tried to import the .sql file into MySQL and it didn't like the syntax of the files that Informix exported.

I just wanted to know the best way to export/dump/output an Informix database that can be imported into MySQL.


texts: Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

texts:
I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), which is a problem when working on huge tables.

Does this apply to ALTER on PG db tables and Oracle db tables, respectively?

What are high-level mechanics of ALTER (I'm esp. interested in PG) that can impact db for huge tables (one legacy Postgres DB I inherited has a table that weights nearly 400GB.., entire DB is ~1TB)?


texts: ODBC in Access working but not in SQL Server

texts:
I have a postgres database running on a remote server.
I'd like to be able to query it in SQL Server.
I downloaded the ODBC Driver for postgres and created a system DSN which successfully runs a connection test.
I created the linked server on my SQL Server and I can see the tables of my postgres database.
However, I get the following errors when trying to query those tables:

1 - select * from [LinkedServer].[postgresDB].[public].[TableName]

The OLE DB provider "MSDASQL" for linked server "[LinkedServer]" supplied inconsistent metadata for a column. The column "Name" (compile-time ordinal 2) of object [postgresDB].[public].[TableName] was reported to have a "DBCOLUMNFLAGS_ISLONG" of 128 at compile time and 0 at run time.

2 - select * from openquery(LinkedServer, 'select * from [postgresDB].[public].[TableName]')

cross-database references are not implemented: [postgresDB].[public].[TableName]

3 - When right clicking on the table and pressing script table to new select window

Cannot get the data of the row from OLE DB provider for linked server (null). Conversion failed because the data value overflowed the data type used by the provider.

To isolate the issue, I did the same thing in MS Access to determine if it was on the postgres side or the SQL side.
In MS Access the data is shown beautifully. Therefore I am of the opinion that there's an issue with my linked server in SQL athough I don't know what it can be. I've created the linked server with all the default configurations.
Any help would be appreciated.


texts:
I am trying to recover a database where the incremental (log) backups are split between two methods (Carbonite/Zmanda and SQL .bak files).

When I try to use the restore wizard I get the following error for the Zmanda files:

  Msg 3201, Level 16, State 5, Line 1
  Cannot open backup device '{062FB61C-74FB-4EEF-9C95-39A84FB69BD3}'. Operating system error 2(The system cannot find the file specified.).

  Msg 3013, Level 16, State 1, Line 1
  RESTORE LOG is terminating abnormally.

Is there any way for me to make these "tape" backups accessible from SQL Server? The Carbonite backups seem to be grouped into single files for about 4 different databases but if there is alternatively a way to extract those logs that would be great.

I do have the full backup. Then after that I have this situation:

Carbonite-1   LSN-1 LSN-2
SQL-1         LSN-2 LSN-3
Carbonite-2   LSN-3 LSN-4
Carbonite-3   LSN-4 LSN-5
...

So I have all of the files I need, I'm just not able to pull the incremental files out of Carbonite since they are in the Zmanda(?) format.

This is basically a case of incorrectly setup backups. The person who set it up was going for redundancy (two sets of incremental backups) but it looks like what actually happened was the backups were split between the two systems because of how SQL Server creates log files (only writes each entry to one place).


texts: SAN Admin vs. DB Admin free allocated tempdb

texts:
I really want to understand. My SAN-Admin told me I have to make sure my TempDB does not grow to max hard disk space.
I have 380 Gig on a dedicated disk, on a dedicated LUN, on a dedicated machine.
My SAN-Admin has a colorful fancy reporting in place, informing him every hour when ever a disk is 80% full. He treats it as crytical and he forwards it to management.

I'm stuck like Don Quijote explaining to everybody, why I don't give a rats a*s about the tempdb disk size. What I want to see instead is the allocated and free space for tempdb. And I'm telling them MS SQL won't free the allocated space on it self because it says, when the space is allocated now it is most likely it will be allocated the next time, again. So why bother allocating it again and again?

I know the SAN Admin treats every server the same, every disk the same. He has got a server farm to manage. There is no difference in DB or simple Web Server out of his perspective.

Now I need you. Is there any perfectly good reason why a any reporting should treat every server, even every DB Server equally, except for the sake of simplicity?


texts: How do I create a table with count of instances of an id before an event date that is individual to that id?

texts:
I am very new to SQL and have a relatively complex query to write, any help would be appreciated.

I have data with the following columns:

ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0)

I need a table with the following three columns:

CUST_ID
A column with the count of the number of orders a specific customer had before their FIRST returned order.
A column with the count of number of orders a specific customer had after their FIRST returned order.

Each order has a unique id as does each customer, but there are many customers who had multiple orders at different dates. Not all customers had returned orders and I just need the counts for the customers who had a returned order (RETURNED=1).


texts: Informatica Source Qualifier not working from Windows ODBC data source (system DSN) from Excel xlsx file

texts:
Reference:

https://community.informatica.com/message/62128

Windows ODBC Workflow:

Windows > Control Panel > Administrative Tools > Data Sources (ODBC) > click "System DSN" menu > click "Add" > choose "Microsoft Excel Driver (*.xlsx) >

Data Source Name: "aaaaaaaaa"

Version: Excel 12.0

click "Select Workbook" button > choose xlsx file

Informatica PowerCenter Designer Workflow:

Sources > Import from Database >

ODBC Data Source: "aaaaaaaaa"

click "Connect" button > the button's name changes to "Re-Connect"

Error:

.. it doesn't seem to find the data in the worksheet ... I have 3 columns with 10 rows of data


texts: Modify the column names of database tables to fit my app's technical limitations?

texts:
I work in a company which works on gigantic (let's say hundreds of gigabytes) databases delivered by 3rd parties. Further, I have to create a data base connection from within a software (that is, an application program) used by the company to load and visualize the data.

The problem

The database has column names character lengths of 32+, but the software app only supports column names lengths of up to 31. So these tables containing the columns can not be loaded, and I can not really work with the database.

The questions

Should I touch the database and shorten the column names everytime it is necessary (for updates etc..) or should I rather push the company to use software which supports longer column names ?

On the one hand, I think it is very ugly to touch the raw data, on the other hand the app licenses the company already bought were very expensive and just switching the sotware might not be such a popular move...

Or does it depend on the particular case? Would the number of affected columns or the price of the software correlate in any way with the decision I would have to make?

Or is there an even better solution for this?


texts: How to store user's language knowledge (150+ languages) in mysql DB

texts:
Users in my website must be able to select languages they know (English, German, etc), so I have a table languages with 150 languages (id, name) and table users (id and a lot of other fields).

Website's visitors must be able to search users by their language knowledge. For example, they might be looking for someone good at English, German and French.

I'm unable to solve this. If I make a table language_knowledge with fields id, language_id and user_id, then I will be able to find users who know one specific language (lets say English), but I need to find users who know, for example, BOTH English and German.

How do I do this?


texts: SQL server on VMWare restart loses five days of data

texts:
I am just now in the midst of a strange issue. A client moved their SQL database server from Hyper-V to VMWare on Saturday. Yesterday they called me complaining about performance. In the brief time and limited access I had I was able to see a lot of indicators of poor I/O (and many other problems) so I made a few recommendations.

They restarted their SQL server to add on the VMWare tools and take care of a few other tasks. Upon restart their data reverted (from their perspective) to the moment they brought it up on the new VM. They found that the new database was pointed to the old iSCSI drives instead of the new vmdk files and wasn't writing anything at all to disk. They tried restoring their backup but it fails every time before completion.

Did the database just fill up the dirty cache, never writing to disk? How in the world did the database continue to function for five days without presenting anything more than slow I/O to the end users? A complicating factor is they didn't have any alerts set up on the database that might have warned them of these kinds of problems. The event logs on the server itself show that there were numerous write errors.


texts: Query to insert data into columns and check for pre-existing values for those columns data

texts:
I have table t1 and t2 with same column names in both tables, column a and b. I want to enter data of column a and b from t2 into t1 at the same time check weather  any data pre exist in t1. If data exist already in a and b column then don't enter that data.
The main challenge in my case is , I want to use combination of (a and b) column data to check for pre existing values.
Thanks for your precious time in advance.


texts: Matching separate unicode characters as one

texts:
I am writing a C# program, and I'm querying a SQLite database. I have multiple diacritical characters (e.g ã, õ, ó, ê) which I need to match in my search. I have the command

"SELECT * FROM DesanoDict1 WHERE Desano LIKE '%" + string + "%' ORDER BY Desano ASC"

However, I have the problem where those diacritical characters are entered in as separate ones (i.e the ã is an a + ~). I could make a custom keyboard with the single unicode characters (as one instead of two separate characters), but the users that will be using the program will probably be using either the same or a similar keyboard to the one I'm currently using, so it will only create problems. I am very new to database and querying, so any help here would be greatly appreciated.


texts: Loop through all databases and remove all users

texts:
We are doing a conversion from one system to another which is requiring backup/restores.  I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.  I have it working for a single database, but when I try and loop through all databases and run my process I have issues.  Does anyone have suggestions on how I can enhance my script to get the results I am looking for.

DECLARE @SqlCommand VARCHAR(4000)
DECLARE @MaxRowNum INT
DECLARE @MinRowNum INT
DECLARE @DatabaseCount INT
DECLARE @MaxDatabaseCount INT
DECLARE @DatabaseName VARCHAR(50)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #DatabaseList FROM sys.databases WHERE database_id > 4 AND [name] NOT IN ('DBAdmin', 'FS_TEST', 'Garry') ORDER BY [name]
SELECT @DatabaseCount = (SELECT MIN(RowNum) FROM #DatabaseList), @MaxDatabaseCount = (SELECT MAX(RowNum) FROM #DatabaseList), @DatabaseName = DB_NAME()

WHILE @DatabaseCount <= @MaxDatabaseCount
BEGIN
SELECT @SqlCommand = 'USE [' + name + ']' + CHAR(13) + 'GO'FROM #DatabaseList WHERE RowNum = @DatabaseCount

EXEC (@SqlCommand)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #UserList FROM sys.database_principals where (type_desc like '%use%' OR type_desc LIKE '%group%') and name not in('dbo','guest','INFORMATION_SCHEMA','sys','public')
SELECT @MinRowNum = (SELECT MIN(RowNum) FROM #UserList), @MaxRowNum = (SELECT MAX(RowNum) FROM #UserList), @DatabaseName = DB_NAME()

WHILE @MinRowNum <= @MaxRowNum
BEGIN
    SELECT @SqlCommand = 'DROP USER [' + name + ']' FROM #UserList WHERE RowNum = @MinRowNum

    print (@SqlCommand)
    PRINT (@SqlCommand + ' --- User Dropped from ' + @DatabaseName)

    SET @MinRowNum = @MinRowNum + 1
END

SET @DatabaseCount = @DatabaseCount + 1
DROP TABLE #UserList
END
DROP TABLE #DatabaseList


texts: What Is The Best Way To Clone A Database From Production To Development Environment?

texts: We use SQL Server at our company as DBMS. I have a scheduled job working monthly. The job restores every databases on production server to development server at the end of the month but there is insufficient disk issue on the development server. I truncate some big tables which are not important for development teams and shrink all databases after the restore completed. Shrinking databases takes too long time to increase free disk space on development server. Of course, this is not good solution. I need a better or the best solution for the situation. I need your advices. How can i use more efficient method? What would be the best solution?


texts: How to backup MS SQL Server databases off-site?

texts:
Microsoft SQL Server supports backing up to remote network locations only by means of UNC names the SQL Server (and user account it's running under) has access to.

Is it possible to create backups and stream them back to a SQL client by means of using the same database connection?

I think you would have to pull the database schema and data, then join those together to create a SQL script that can be used to recreate the database being backed up, but is this a viable solution to backing up SQL Server databases?

PS: I know there are commercial solutions that offer off-site back-ups but I'm not interested in commercial solutions at the moment.


texts: Is it possible to convert a .mysql file to a .sql file?

texts:
I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop.

When browsing for my dump file, this application filters for .sql, .sql.gz and .mysql files. I thought it would load my .mysql easily, but I get an unsupported file type .mysql error.

Is there a way to convert my .mysql file into a .sql file, knowing that I don't have access to the original database used to generate my mydump.mysql? I would like to try to feed a .sql file to Acquia Dev Desktop instead.

P.S.: I am on Windows 7 if this is relevant. Thanks.


texts:
I joined a new company this week where I noticed that one always on setup has listener ip configured, while the other doesn't. For the AG setup which doesn't have the listener in it, will the applications connect to the cluster ip address? Can i add the listener at this point of time? What are the HA options if Listener is not present?

First two screenshots show one AG setup without the listener

For the AG setup with the listner and the fileshare witness


texts:
I try to create one test procedure but it gets
Error(4,1): PL/SQL: SQL Statement ignored ** and ** Error(12,20): PL/SQL: ORA-00942: table or view does not exist when I try to compile. Then I test select statement in next session, it run successfully. Could you please help me.

create or replace PROCEDURE TEST_1
AS
BEGIN
SELECT
A.EMPLOYEE_ID AS "Employee ID",
A.FIRST_NAME||' '||A.LAST_NAME AS "Name",
B.DEPARTMENT_NAME AS "Department Name",
A.EMAIL AS  "Email Address",
A.PHONE_NUMBER AS "Contact Number",
C.STREET_ADDRESS||', '||C.CITY||', '||NVL(C.STATE_PROVINCE,C.CITY) AS "Address"
FROM HR.EMPLOYEES A
LEFT OUTER JOIN HR.DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
LEFT OUTER JOIN HR.LOCATIONS C
ON B.LOCATION_ID = C.LOCATION_ID;
END;


texts: MySQL Query running slow when using REPLACE function instead of CASE statement

texts:
Is it better to use CASE statements instead of REPLACE functions in MySQL when mapping a comma-separated-string field?
The below query runs extremely slow using replace.
Note, the underlying user_roles table is of format [user_id (bigint), string_of_user_role_ids (varchar(200))]
-- this runs slowly
select      string_of_user_role_ids
            , replace(replace(replace(replace(replace(string_of_user_role_ids,
    '10', 'Scientist'), '9', 'Superhero'), '8', 'Teacher'), '7',
         'Journalist'), '6', 'Farmer')
            , count(1)
from        user_roles
group by    1,2
order by    3 desc

-- this runs quickly, but is more difficult to keep adding in multiple new when clauses whenever a new user role is added
select      string_of_user_role_ids
            , case  when string_of_user_role_ids= "6" then 'Farmer'
                    when string_of_user_role_ids= "7" then 'Journalist'
                    when string_of_user_role_ids= "8" then 'Teacher'
                    when string_of_user_role_ids= "6,7" then 'Farmer, Journalist'
                    when string_of_user_role_ids= "6,8" then 'Farmer, Teacher'
                    when string_of_user_role_ids= "7,8" then 'Journalist, Teacher'
                    when string_of_user_role_ids= "6,7,8" then 'Farmer, Journalist, Teacher'
                    -- ... etc.
                    else 'Unknown' end as app_user_type
            , count(1)
from        user_roles
group by    1,2
order by    3 desc

Ideally I would use the REPLACE function instead of a CASE statement, as it seems easier to scale out in terms of expanding the code and less risk to manage.
I can't understand why one query runs quickly and the other very slowly (seconds versus minutes, after a few mins I killed the slow query).
Ideas/questions are welcome please.
Explain statement output for both is the same:


texts: How to import sqlite from plain text dump?

texts:
This excellent XKCD article about color-poll data at the very end mentions:

  If you have ideas and want to analyze these results further, I’ve posted the raw data as an SQLite dump here (84 MB .tar.gz file).

In the archive you will find plain .TXT file mainsurvey_sqldump.txt which is SQLite dump. I work on MAC and would like to turn this dump into a database. How can I do it and what friendly software can I use? I am very beginner at SQLite and DB. I tried DB Browser for SQLite (at sqlitebrowser.org) but could not figure out how to import the dump (what I tried runs forever).

Thank you very much for any tips.


texts:
Sorry for a silly question, I tried query with WHERE clause and ORDER at the end, which actually took very long time to implement, even thought WHERE clause was on indexed field. It looks like Mysql firstly performs ORDER on entire table and after that SELECT, which is not preferred behavior. Am I right? How can I cause it first perform SELECT and then ORDER.

Thanks!

Addendum:

mysql> explain SELECT *
               FROM `products`
               WHERE (`products`.user_id = 1111)
                 AND ( (`products`.`type` = 'type1' ) )
               ORDER BY products.id DESC
               LIMIT 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
         type: ref
possible_keys: index_products_on_user_id, index_products_on_type
             , index_products_on_type_and_status
          key: index_products_on_user_id
      key_len: 5
          ref: const
         rows: 2570
        Extra: Using where; Using filesort


texts: Trying to replicate 2 or more Postgres instances to one standby host

texts:
My setup has 2 Postgres hosts:

host SRV1 as primary with 2 instances and IP 192.168.1.241

Instance A listening on port 5432
Instance B listening on port 5433

host SRV2 as standby with instance C and IP 192.168.1.243 using default port.

I'd configured sucessfully replication from instace A to instance C using repmgr.

I'm using PostgreSQL 10 and rempgr 4.

How could I configure replication from new instances, e.g. B, to a new instance on host SRV2 (standby) using repmgr?


texts: No tables found after running Expdp and Impdp in Oracle 12c

texts:
I am trying to export a schema which only contains one table with one row on it.  It has a table called "product" and resides in a tablespace called "tbs_data".  Here's the expdp command I ran which completed successfully.

expdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

When I ran the impdp, it complained about the "myschema" missing.

impdp dbauser/dbapasw@oradev schemas=myschema directory=my_data_pump_directory
dumpfile=myschema_exp.dmp logfile=myschema_exp.log

So I created this user/schema manually and re-ran the impdp command.  It completed successfully.  However when I logged in as "myschema", I didn't see the product table.  Also, from what I have been reading, the Impdp command
was supposed to re-create the target user/schema when you run Impdp.

Could someone let me know what I am missing or how to resolve this?

Here's the actual log:

;;;
Export: Release 12.1.0.2.0 - Production on Fri Jul 29 14:22:46 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "dbauser"."SYS_EXPORT_SCHEMA_01":  dbauser/********@oradev schemas=myschema directory=my_data_pump_directory dumpfile=myschema_exp.dmp logfile=myschema_exp.log
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "dbauser"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for dbauser.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\ADMIN\ORADEV\DPDUMP\myschema_EXP.DMP
Job "dbauser"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 29 14:22:54 2016 elapsed 0 00:00:07


texts: mysql upgrade from 5.6 to 5.7, how to config mysql backup

texts:
we have a lot of mysql database running from 5.6 to 5.7 , we use netbackup to backup mysql ,
the local script  (mysql_backup.sh) is as belows：
/opt/mysql/meb-4.1.3/bin/mysqlbackup --login-path=root   --backup-image=sbt:bkpsbtNB$backuptime --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --sbt-environment="NB_ORA_SERV=pnbumaster,NB_ORA_CLIENT=prmbsage_svc,NB_ORA_POLICY=mysql_cdeskdb,NB_ORA_SCHED=Default-Application-Backup,ORACLE_HOME=/age/mysql" --backup-dir=/usr/openv/meb_bkdir/ backup-to-image  --socket=/age/mysql/data-5.7.29/mysql.sock --port=3306 >> $MYSQL_LOG_FILE
every time we have upgrade database , we should re-config mysql_backup.sh
is there any method to auto config mysql backup?


texts:
I am trying the fetch the data of the each user of each month. The table looks like below

----------------------------------------------------------------------------------
| user         |  pm_jan      |       actual_jan    |       pm_feb  | actual_feb |
----------------------------------------------------------------------------------
|   u1         |  w5           |     6               |   0           |    0       |
|   u2         |  w2           |     5               |   0           |    0       |
|   u3         |  w6           |     2               |   w7          |    7       |
|   u4         |   0           |     0               |   w6          |    2       |
|   u5         |   0           |     0               |   w2          |    3       |

In the above table now I want to fetch the total count of  pm and actual data of each month until december.

I tried the below but I am able to fetch only the count of jan month but I need it for every month count

    SELECT COUNT(pm_jan),COUNT(actual_jan)
    FROM pm
    WHERE  pm_jan<>'' AND actual_jan<>''

What is the best query to approach this method


texts: How to lock entire Postgres table but allow reads

texts:
Sorry for the noob question, but what is the correct table level lock mode to allow reading from a table but not writing to it?

The scenario that I have is that I want to prevent two tables from being written to while a third table is receiving an update. As soon as that update completes it's okay for the other two tables to unlock and receive additional updates.

If it isn't possible to prevent those tables from being written to while still allowing them to be read from then I could also just lock them entirely so they can't be written to or read from during the lock.

Here is the documentation page, I just don't understand what it's saying: https://www.postgresql.org/docs/current/explicit-locking.html


texts: Database Mail Configurations using gmail account

texts:
After creating a profile and account using gmail account
smtp.gmail.com port 587
I get an error while trying to send a test email

  The mail could not be sent to the recipients because of the mail
  server failure. Cannot send mail to mail servers(The SMTP server
  requires secure connection or the client was not authenticated).

Command for sending

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',  -- my profile name
    @recipients = 'myaddress@gmail.com',  -- my email address, the same one
                                          -- which is also configured above
    @body = 'This is a test email sent from TEST server',
    @subject = 'TEST EMAIL',
    @importance ='HIGH'

What I am doing wrong? Or should I change some settings from in my gmail account settings?
Thanks


texts: Flip a flag from one row to another in a single query

texts:
Given this table:

CREATE TABLE rollouts
(
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id    TEXT        NOT NULL,
    is_active    BOOLEAN     NOT NULL DEFAULT FALSE
);

How can I flip the is_active flag from one row to another for a given device_id and id?

Eg, go from

ID    DEVICE    IS_ACTIVE
1     D1        TRUE
2     D1        FALSE

to

ID    DEVICE    IS_ACTIVE
1     D1        FALSE
2     D1        TRUE

in  single query?

Because this won't clear the flag for ID=1:

UPDATE table SET is_active = true WHERE device=d1 AND ID=2


texts:
I am trying to backup mysql using the command
mysqldump -u root -p  database_name > backup.sql

, but it is throwing an error:

'Access denied; you need (at least one of) the PROCESS privilege(s)
for this operation' when trying to dump tablespaces

Also, I wanted to make a backup my database with all tables seperately stored as a file. How can I do it?
mysql version = 5.7.31


texts: Grouping Subsets of Rows with Null Values within an Ordered Set

texts:
Lets say we have an table where each row is a day, and it is ordered by this day column. Then we have left joined a membership data set showing which day the members were active (and not).

Lets say our current data set looks like this... Membership was active from day 3-5, inactive from 5-8, and active from day 9 onward etc.

DAY     DATE        MEMBER  ACTIVE
 1      2017-01-01  123     null
 2      2017-01-02  123     null
 3      2017-01-03  123     2017-01-03
 4      2017-01-04  123     2017-01-04
 5      2017-01-05  123     2017-01-05
 6      2017-01-06  123     null
 7      2017-01-07  123     null
 8      2017-01-08  123     null
 9      2017-01-09  123     2017-01-09
10      2017-01-10  123     2017-01-10

...so ACTIVE=null means membership was not active on those days.

With this data structure, I would like to get to a "collapsed" set, showing "spans" of time inactive/active:

MEMBER  MIN(DATE)   MAX(DATE)   STATUS
123,    2017-01-01, 2017-01-02  INACTIVE
123,    2017-01-03, 2017-01-05  ACTIVE
123,    2017-01-06, 2017-01-08  INACTIVE
123,    2017-01-09, 2017-01-10  ACTIVE

I have tried using row_number() to somehow partition out the subsets of a certain status, but in this case, using min()/max() over the rows where ACTIVE is null, treats those as a single group, when in reality, there are several distinct spans of "inactive membership".

How can I distinguish the spans of inactive membership from one-another for grouping purposes?
What technique can I use to achieve that output above?

Here is the script to generate the dummy source data:

CREATE TABLE ##SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO ##SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION
SELECT 2, '2017-01-02', 123, NULL UNION
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION
SELECT 6, '2017-01-06', 123, NULL UNION
SELECT 7, '2017-01-07', 123, NULL UNION
SELECT 8, '2017-01-08', 123, NULL UNION
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION
SELECT 10, '2017-01-10',    123, '2017-01-10'
;


texts: How to Replicate data between a hosted database and a local database

texts:
I have a Cloud hosted Database, maintained by a different company.  Here is my scenario:

Cloud Hosted Database SQL Server 2012 - Need to connect through a VPN.
We setup a local Server with with Dual NIC cards.  This allows us to have the    VPN connection, and the local network connection.
I would like to have a local copy of the cloud hosted database on our server.
I cannot setup replication inside SQL server, I do not have permissions on the cloud server.
I cannot do a back-up and restore of the database - I do not have permissions on the cloud server.
I cannot use triggers
I cannot use logs.
I cannot use back-ups

How can I bring down the entire database, schema and data, and keep my Local database updated real-time?

Edit:  I was able to get a script run from the hosting company so I could create the database along with the schema and initial data.

The most important question is how can I keep the data synchronized real-time?  Right now, i'm thinking of doing a series of Jobs in SQL to query the Linked Server database with something like this:

insert into local_table_1
select * from linkedserver_table1
except
select * from local_table_1

The problem is, it's not efficient, and it will not pull any of the updated records.  We do have an "Updated_date" field.  How can I update the local server, selecting all records from the Linked server, and update the local database?


texts:
The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles.
But while inserting into table only one redo directory is used.
I ran following command to get redo information "all dump 2398" and i get:

REDO part: 0 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)
REDO part: 1 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)
REDO part: 2 HEAD: file: 1 mbyte: 0 TAIL: file: 2 mbyte: 2 total: 12 free: 6 (mb)
REDO part: 3 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)

It doesn't uses REDO part: 0 REDO part: 1 REDO part: 4 . It only uses REDO part: 3
It will give "ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER" inspite of using rest of redo files.
Why should we allocate 4 times space if it only uses 1 of 4 redo files?


texts: write SQL query that takes its own output as input

texts:
In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:

713613  PostPTETradeIDs 718478
713613  PrePTETradeId   711314
718478  PostPTETradeIDs 739522
718478  PrePTETradeId   713613

In this example, trade 718478 was created by splitting 713613 (row 1); and 739522 was created from 718478 (row 3), etc. (a PTE splits a trade into two).

select trade_id, keyword_name, keyword_value
from trade_keyword
where (keyword_name like 'LinkedTo' or keyword_name like 'PrePTETradeId' or keyword_name like 'PostPTETradeIDs' )
    and trade_id in('718478', '739522','713613')
order by trade_id;

My goal is to identify trades that belong together and come from one another.

Currently, using the above query, I must fill all the TRADE_IDs manually, i.e. start with 713613, run the query, see that 718478 came from it, then run the query again searching for 718478, too, to find that 739522 came from it, etc.

I would like to make this recursive, such that it will, in one query, given some starting TRADE_ID(s), find all TRADE_IDs that are related to these via any path of any of these relations.

In other words, if column 1 is X and column 3 is f(X), then I want at once a list of all X, f(X), f(f(X)), f(f(f(X))), etc.

Preferably, there would be another column that lists the distance in steps upwards or downwards, where the PostPTETradeIDs relation is upward and the PrePTETradeID is downward (the LinkedTo relationship does not change the counter):

713613 is distance 0,
718478 is distance 1,
739522 is distance 2.

I am using Oracle SQL. Is this issue better adressed via a subquery, and if so, how can it be made recursive; or some other functionality?


texts: Query tables with 400 million records fast

texts:
Table: Customer Data
Rows: 400 million
Indexes: Non-clustered (as per queries)

Issue: Index Scan happening with most queries, 400 million scan take too long to complete
Need advise on how to address above problem:

Will partitioning the table by YEAR help improve performance or partitioning only helps in data management?
How to convert Index Scan to Seek (majority of reports using different columns, filters and aggregation)
Or simple approach: Keep current year in one table and rest all archived in historical table would be best
Any other feature in SQL Server 2019 which can be used when queries with such big data can return records faster.


texts:
I'm trying to migrate an application from SQLite to PostgreSQL, and now I have reached the point of re-expressing the queries that are valid in one and invalid in the other.

I have a table:

CREATE TABLE wage_histories (
  uid INTEGER REFERENCES users ( id ) ON DELETE CASCADE NOT NULL,
   start TEXT NOT NULL,
   wage INTEGER NOT NULL,
   PRIMARY KEY ( uid, start )
);

It represents the wage of a user, over time. So, the wage of a user on a particular date is the value of the wage column in the row with our chosen uid and the highest start that is less than our chosen date.

I was able to express this in SQLite with the following query:

SELECT wage, MAX(start) FROM wage_histories WHERE start <= ? AND uid = ?

However in PostgreSQL this gives me the following error:

ERROR:  42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function

Is there something more succinct than the following?

SELECT wage FROM wage_histories WHERE uid = ? AND start = (SELECT MAX(start) FROM wage_histories WHERE uid = ? AND start <= ?)