texts: MySQL Master/Slave replication on same VM instance?

texts:
I'm fairly new to databases so I hope this isn't a silly question!

Is it possible to set up master/slave replication on the SAME server?

I understand there are not any advantages to this in terms of hardware utilisation/recovery purposes, but a set up like this would help test a piece of software I am developing.


texts:
This may sound like an overly simple question but I'm not finding it easy to locate a proper answer.

To the question "What are sql clauses?" most of the resources on internet simply provide a list of clauses and explain what they do.

But I'm trying to understand in abstract terms how it is defined. Like a generic notation that captures all kinds of queries that can be written and how clauses fit in there.

Are there properties common to all clauses? Why is UNION called an operator whereas HAVING is a clause?


texts: Database structure for a bilingual dictionary

texts: I've search all over and can't find advice of a good database structure for a bilingual dictionary, how would you define the structure for an English-Spanish language bilingual dictionary?


texts: How to create Sql Server login for a domain account?

texts:
All,

I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I want it to be an admin on the Server1. And then create a login on Sql Server for that Domain account (not local account).

How do I do that?

Questions:

The user AD\Sql1, I want him as Admin on Server1. For that, do I just create a local account with the same user name (let's say, Server1\Sql1) and make the local account an Admin on the machine. Will that local account be automatically mapped to the domain account with the same name?
May be I should add here that CREATE LOGIN [AD\Sql1] FROM WINDOWS did not work for me. I tried and got an error saying user does not exist in Windows (something like that)
Now, if the above technique works. Lets assume, AD\Sql1, becomes an Admin on Sever1. So, in theory I do not need to create a Sql Login for that Windows Domain account on the Sql Server, right? All the local admins that are part of BUILTIT\Admins automatically have sysadmin access to Sql Server, right?

Where did I go wrong? Please share any URLs that could explain this concept better.

Edit: I should also add that Question 1 and 2 are important independently. I know how to add a domain/windows account as sysadmin. But I am interested in how mapping works between Domain and local accounts. My goal here is not how to add a local account as sysadmin, I can do that pretty quickly. My intention is not to be rude, but be as clear as possible. If my question is still not clear, please let me know, so I could add more details.

Thanks,
_UB

Edit: Grammar


texts:
As a solution for replication, to protect VM, LUNs and recovery including database, EMC Recoverpoint was presented by our vendor.
But in EMC paper's, the only one database related example ilustrates Vplex Metro with Oracle RAC.
My topology will use Postgresql.

Anyone have already recovered some database using EMC Recoverpoint?


texts: Will the Query Optimizer Ignore A Fragmented Index?

texts:
Scenario: I have a heavy OLTP table with an index.  I see many inserts, updates and deletes and the index fragments heavily within a day or less.  While on day one of the index build, the optimizer uses the index, day two or three, the optimizer skips it entirely. This is for exactly the same query.

Question in my head: why would some query plans skip the index, since the index is created to help optimize these plans?

Question for this post: can the optimizer skip a heavily fragmented index, such as a scenario where we have 1 billion records and an index is built, then two hours later, all billion records are removed and we have five hundred million new records?

I'm beginning to think that adding an index to this table won't help at all, because of the nature of the table (data in quickly, data out quickly), but just want to understand why one day one, the optimizer will use the index in its plans, but day two, it won't.


texts: Is MySQL Clustering a viable option for a DB expecting 5TB of data in 5 years?

texts:
We plan to implement MySQL Clustering.The database should support 5 years of data and expected data/year is 1TB.
When we created a test enviornment with the DB size as 200GB, NDB Cluster took more than 2 hours to restart.

Is MySQL Clustering a viable option for an application that expects 5TB of data in 5 years, if yes, could you please guide on the suggested architecture and configuration for such a setup.


texts: Column name on NOT NULL constaint violation not shown

texts:
I'm using an Oracle 19 DB that is structured into multiple layers by using DB users. Here is a simplified overview of the structure:

User DATA: Has the table XTEST and the view TEST. The view is created with a CREATE VIEW TEST AS SELECT * FROM XTEST statement, so it contains all columns of the table.
User APP: Uses the view DATA.TEST for all SQL and DML.
The APP user does not have any grants on the table DATA.XTEST, but does have SELECT,INSERT,UPDATE,DELETE grants on the view DATA.TEST.

Now to my problem:
If a NOT NULL constraint on the table DATA.XTEST is violated, the error message doesn't show the column name. The error message is literally:

ORA-01400: cannot insert NULL into (???)

The DB used Oracle 11 some time ago and back then we had the same problem, but could fix it by granting the REFERENCES privilege on the table DATA.XTEST to the APP user (see related question: Grant privilege to see column names in null constraint violations when inserting in views). But this doesn't work any more in Oracle 19.
Does anyone know how to get the column name in the error message?


texts: Where does MySQL's "show slave status" information come from?

texts:
two files "master.info" and "relay-log.info" were deleted after I "reset slave"

I want to know why slave information still exists ~ confused >_<

I think it shouldn't display any message about slave ~ thanks ^_^

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: centos7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: Centos6-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 126
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)


texts:
I am currently working on below table structure to pull the data/ generate any one time report.

Table ABC:

ID  Field       Value
1   Name        Nagarjuna
1   Gender      Male
1   Industry    Actor
2   Name        Sachin RT
2   Gender      Male
2   Industry    Sports
3   Name        Theresa
3   Gender      Female
3   Industry    Social Worker
4   Name        Saha
4   Gender      Female
4   Industry    Software Engineer
5   Name        Swe
5   Gender      Female
5   Industry    Doctor

In order to get the report in the format Name,gender,industry values, I am writing the below query

Select
  vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock)
join ABC [vgender] with (nolock)
  on vname.id = vgender.id
join ABC [vindustry] with (nolock)
  on vgender.id = vindustry.id

where vname.field = 'Name'
  and vgender.field = 'Gender'
  and vindustry.field = 'Industry'

I am currently working on the table which has around 500k of records and for each id type there would be around 10 - 30 entries, so if more records are there for one id type then more joins would be added up.

Because of this executing it is taking a lot of time.

Is there any way that we can reduce this time taken for execution and also this table is used by an website where there would be around 1000 to 50000 users.


texts: I'm in one security company and our history data grows fast, causing data archiving sqls run slower and slower. Say, for the sake of data real-time need, the scripts must run in a high frequency for one time per 100s, causing performance issue, and what worse is that data stored in different DB engine(MySql and SqlServer), So I wonder is there any better way to archive financial DB data except traditional way(ODS way), which run archiving data sql scripts frequently?


texts: Efficiency for contact-contact relationships schema

texts:
I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the people table has about 90k records with 80k relationships to 10k organisations.

New model:

Contact           Relationship              (Contact table again)
--------          -------------             ---------------------
cid      11----0< cid_a              /---11 cid
name              cid_b          >0-/       name
                  details
                  start_date
                  end_date
                  relationship_type

If I want to be able to query for Wilma's current relationships (let's say Wilma has cid = 2) I can set up 2 keys on relationship, one (cid_a, cid_b) and (cid_b, cid_a).

SELECT friend.name FROM contact friend, relationship
WHERE
     (
       ( cid_a = 2 AND cid_b = friend.cid )
       OR
       ( cid_b = 2 AND cid_a = friend.cid )
     )
     AND
     ( start_date IS NULL OR start_date <= CURRENT_DATE )
     AND
     ( end_date IS NULL OR end_date >= CURRENT_DATE )

But I'm not sure it's efficient as the duplicate keys would be quite long.

A contact will likely have 3, 4 or more relationships to various organisations, other contacts etc. such as

Wilma is a student at X University-
Wilma is a member of Y organisation
Wilma was previously a contact at Z organisation
Wilma is married to Fred.

Is this the One True Way? Or nothing like it?!


texts: Confused about why a particular index is being used

texts:
I have a table (with 170M rows) that looks as follows:

CREATE TABLE [dbo].[Panel]
(
    [SubId] [varchar](15) NOT NULL,
    [LineageId] [int] NULL,
    [Buck] [varchar](20) NULL,
    [Lot] [varchar](20) NULL,
    [GlassType] [varchar](20) NULL,
    [ETA] [varchar](200) NULL,
 CONSTRAINT [PK_Panel] PRIMARY KEY CLUSTERED
(
    [SubId] ASC
)

99% of the queries against this table reference SubId either in the Where clause or join. One of our DBAs told me he could make all those queries and joins perform better by creating the following index:

CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId])

When he told me this, I thought he was nuts. But I just checked index usage since this index was created and found the following:

PK_Panel (232,394 seeks / 2,133 scans)
IX-Panel-SubID-I-LineageID (25,528 seeks / 3644 scans)

I was a bit shocked to see this. Under what circumstances would this new index every get used? Why would SQL Server ever select it?

Or maybe a better question would be, why would SQL Server select the new index to do a seek, instead of the clustered index? Approximately 25K times, it thought seeking on the new index was a better choice.

In case this helps, the LineageId essentially indicates where a panel was created and there are ~35 distinct values it could contain.


texts: How to get the row number when using alias in orderby

texts:
I have a query. I use an alias in order by when using row_number and I got

  [42703] ERROR: column "total_comments" does not exist error

How can I fix this?

 select
      cr_seller_history_id,
      c.created_at,
      company_name,
      business_name,
      brand,
      kep_mail,
      address,
      phone,
      mail,
      slug,
      name,
      point,
      contact_positive,
      contact_negative,
      product_number,
      (product_positive + product_negative) as total_comments,
      ROW_NUMBER() OVER(ORDER BY total_comments) as rank
    from cr_companies a
      INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id
      INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id
      WHERE DATE(c.created_at) = DATE 'yesterday'
    ORDER BY total_comments DESC NULLS LAST


texts: sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

texts:
These catalog views seem to show the exact same data.  What is the point of having multiple system views that seem to show the same thing?

Here is how I proved it is the same data:

select
    fil.name as [FileName],
    fg.name as GroupName
from sys.database_files fil
inner join sys.filegroups fg
on fil.data_space_id = fg.data_space_id

select
    fil.name as [FileName],
    fg.groupname as GroupName
from sys.sysfiles fil
inner join sys.sysfilegroups fg
on fil.groupid = fg.groupid


texts: How to rollback changes made to the database by a specific user?

texts:
In mssql, is it possible to rollback changes made by a specific user to the database?
For example if that user ran an update query and did not write begin transaction before, is it possible to revert changes made by his query WITHOUT restoring the entire Database (I want to preserve changes made by other users)?


texts: Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of preventing any inserts, deletes, updates, etc. Just thinking in terms of scheduling my maintenance plans, etc.


texts: SQL: Merge rows when value doesnt change in continuous time

texts:
I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during a continuous time (EndDate of row is same as StartDate of the other), for example, for this case:
Dataset

I need to output this:
Desired Output Table

I cant come with a solution yet since I think I cant use Group By, I need to merge rows with specific SerialNumber when the StatusId doesn't change in a continuous time.


texts: Accurately simulate SQL Server work load with diskspd

texts:
We were building a new node in an alwayson availibility group and this new server was on a different storage than the other nodes in the cluster.  Since it was a separate storage we wanted to make sure the performance was comparable to the other nodes.  The storage engineer told that the new server was on flash storage and should be considerably faster than the old one which was using HDD.  We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different.  Since we did not lose performance we decided to go ahead anyway and added the node to the existing cluster and moved some reports to that server and to our surprise it was much faster.

So I decided to do some simple test.  I took a table around 200GB and ran the below query to do a clustered index scan

select count(*) from Tabletest with (index(1)) option (maxdop 6)

When I checked on the old server the perfmon counters were something like this

On the new server with flash storage it was

We can see the lower latency and higher reads/sec and get an idea of why the new flash storage is faster.
Now my question is what parameters do we pass in to diskspd to validate this kind of behavour.  We ran diskspd with the below parameters.

diskspd -b8K -d60 -o32 -t8 -Sh -r -w0 -L -c5G G:\MP16\DATA\iotest.dat

New node is the one with flash storage.

We tried different file size and different block sizes.  Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could.  Screenshot below on old storage with below run

diskspd -b64K -d60 -o32 -t80 -Sh  -w0 -r  -L -c10G G:\MP10\DATA\iotest.dat

So how do we accurately simulate using diskspd a sql server workload(eg clustered index scan)?  Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san?  I read about -Z parameter but it seems to be applicable only for write tests.  Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?


texts: How to properly implement compound greatest-n filtering

texts:
Yep, more greatest-n-per-group questions.

Given the a table releases with the following columns:

 id         | primary key                 |
 volume     | double precision            |
 chapter    | double precision            |
 series     | integer-foreign-key         |
 include    | boolean                     | not null

I want to select the compound max of volume, then chapter for a set of series.

Right now, if I query per-distinct-series, I can easily accomplish this as follows:

SELECT
       releases.chapter AS releases_chapter,
       releases.include AS releases_include,
       releases.series AS releases_series
FROM releases
WHERE releases.series = 741
  AND releases.include = TRUE
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST LIMIT 1;

However, if I have a large set of series (and I do), this quickly runs into efficiency issues where I'm issuing 100+ queries to generate a single page.

I'd like to roll the whole thing into a single query, where I can simply say WHERE releases.series IN (1,2,3....), but I haven't figured out how to convince Postgres to let me do that.

The naive approach would be:

SELECT releases.volume AS releases_volume,
       releases.chapter AS releases_chapter,
       releases.series AS releases_series
FROM
    releases
WHERE
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499,
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137,
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768,
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255,
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606,
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY
    releases_series
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST;

Which obviously doesn't work:

ERROR:  column "releases.volume" must appear in the
        GROUP BY clause or be used in an aggregate function

Without the GROUP BY, it does fetch everything, and with some simple procedural filtering it would even work, but there must be a "proper" way to do this in SQL.

Following the errors, and adding aggregates:

SELECT max(releases.volume) AS releases_volume,
       max(releases.chapter) AS releases_chapter,
       releases.series AS releases_series
FROM
    releases
WHERE
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499,
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137,
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768,
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255,
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606,
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY
    releases_series;

Mostly works, but the issue is that the two maximums aren't coherent. If I have two rows, one where volume:chapter are 1:5, and 4:1, I need to return 4:1, but the independent maximums return 4:5.

Frankly, this would be so simple to implement in my application code that I have to be missing something obvious here. How can I implement a query that actually satisfies my requirements?


texts: How to create a table dynamically based on json data?

texts:
I have a json that I get as a file, I have loaded that in the Postgres database.
Here is the json data:

{
"tablename": "test",
"columns": [
    {
        "name": "field1",
        "datatype": "BigInt"
    },
    {
        "name": "field2",
        "datatype": "String"
    }
]

}

Now I have to create a table dynamically, I am thinking of writing a function in Postgres to do that.
So the table would be named test with 2 fields one as string and another as bigint.

I am able to get the table name by doing a select as below:

select (metadata->'tablename') from public.json_metadata;

However, I am having difficulty getting all the nested column names to form a create table statement.

1- How would you go about doing that, any built in Postgres functions to extract that.

2- Is a Postgres function the best way to approach this problem, or should I write this in python (I will have to learn Python) or shell script.

The number of columns would not be fixed, different json files will have different number of columns.


texts: Programatically copying data from table from offline database to online database

texts: I have a local database and an online database. When i make an entry into my local database, it should also automatically make an entry in the online database. Can anybody tell me how I can do that?


texts: Changed MyISAM to InnoDB, huge performance loss

texts:
So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss.

Here is my.cnf:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instance=2
innodb_log_file_size=512M
key_buffer_size=24M
#70% of available RAM
innodb_buffer_pool_size=3072M
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000

I'm running a Amazon CentOS t2.medium instance (2 vCPU & 4 GB ram).

Here are a few tests I ran. I ran alot more but with the same theme. InnoDB takes way longer and loads the CPU more.

Anyone have any thoughts? I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.


texts: Restore multiple databases quickly - practicing disaster recovery

texts:
I am currently working administering a SQL Server 2014 Standard Edition instance for Sharepoint 2013. We started 5 month ago with ~25 DB's and now we have 60 in total. So the databases in use are growing. In case of a recovery I would have to restore all my collected backups quickly.

Until now I would copy the name and put it into a single script. But in a real disaster situation I would love to be prepared better. How do you manage this?
Do you have a tool or anything which registers the newly created DB's?


texts: Clustered index scan in query with non clustered index filter

texts:
I have an Operations table with a nonclustered index on my foreign key to a Organizations table; however, when trying to run the following query, SQL Server does a clustered index scan instead of using my non clustered index:

SELECT OpType, UserId, OpTime
FROM Operations
WHERE OrganizationId = 'some uniqueindetifier'

I tried doing a join or using in instead and the result was the same. If I change the query to SELECT OrganizationId FROM... then I actually get my intended nonclustered index seek, but naturally returning only this column would be pointless and adding any other column goes back to a clustered scan.

There's dozens of millions of rows in the table but there should be around only 50 000 rows with the OrganizationId I'm looking for, so I'm sure that a query plan that uses the non clustered index would be faster.

I executed the same query for a different OrganizationId (that returned about 30 000 rows) and got the result in less than 2 minutes, while doing this query for the OrganizationId that does a clustered scan didn't finish in 8 minutes even though it should return around 50 000.

I don't have any columns included in the Organization index but I don't want to double the size of this fairly large table by adding all columns to the index as there's a lot of rows getting inserted and I only want to extract all columns from it once a month for a report.

So aside from including columns to the index, what else can I do to make SQL Server use my non clustered index?


texts: Merge Replication customize synchronization cycle and its performance

texts:
I'm trying to implement Merge Replication between just two servers, a publisher and one Subscriber.
Assume these tables :
--Main table in Publisher
Ticket (id, userId, reqId, blockedDate, usedDate, returnDate, transferDate, ...)

--On Subscriber (The fields are in Publication)
Ticket (id, userId, blockedDate, usedDate)

We must assign current date to transferDate whenever a row goes to Subscriber, and assign to returnDate whenever it comes back from Subscriber. As we HAVE to consider a file syncing (transfer/return data to/from subscriber with a USB stick!) these fields must be assigned on syncing either on file syncing or on replication.
I know you can add your custom business logic for Conflict Resolution (when a conflict occurs). I'm looking for something like triggers for replication events, which gain me the ability to manipulate some fields out of Publication (or even another table whenever needed).
Questions

Is it possible to handle replication events(push and pull) manually?**
(customize or override PULL/PUSH's. Not conflict resolver)**

Can you give me a comparison between implementing a customized merge replication and syncing with help of a distributed transaction SP?

I need synchronization to be scheduled (at midnight when there's no load on DB server), BUT it is highly needed we'll be able to sync manually (from within our web application).
In second question I need their performance, reliability, and of course their maintainability. These factors especially the first two, are a big concern. Suppose Publisher may have about millions of records, and Subscriber may have about several ten thousand. So with each synchronization, about several ten thousand records will be transferred.
Merge replication checks rows; if there's a change and there's not a conflict, the row merges. if there's a conflict, a conflict resolver (could be your custom stored procedure resolver) handles the conflict. I want to write my own logic in the first case (data changed without conflict) instead of SQL's built-in merge mechanism. Clearly I want to customize it.
There's onlyid, userId, blockedDate, usedDate columns in the article. I want to fill returnDate whenever a Ticket comes back to Publisher (on a pull) and fill transferDate whenever a Ticket goes to Subscriber (on a push).


texts:
SQL Server 2014 is sufficiently new that not a lot of the 3rd party tooling has caught up with it yet. I'm looking at Red Gate and TOAD in particular...

Is there any way of putting a version lie shim in place on the server until these tools have released newer versions?

EDIT: 2014-05-29 16:00 IST

In response to the hold I'd like to emphasise that this question is about getting the server to lie about its version so that all tools will which have worked to date should continue to work.

Even though two particular vendors have been mentioned this is just by way of example and it should be stated that I'm not looking for a fix for the particular issue shown in the screenshot - that's just there to illustrate my point.


texts:
Dears, I have an problems with a calculation of this formula:

Column "A" has always the same value for a group of SubIDs.
In the "Result" column I need formula (function) like in the example columns "Result Formula Description".
I tried many approaches but each of them help me with a half of the problem.
Please, help me!


texts: What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?

texts:
I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the slaves takes over as master?
One of the things that I wonder, and no one seems to mention anywhere in either tutorials or documentation is, what if I restart the former slave (now master) after this is done? Won't things get messed up, because the .conf file still reflects the old slave setup (e.g. hot_standby = on)? Should I update that .conf, before I can safely restart the server?

Background: The situation I'm in, that made me wonder all of this is that I need to change the HDD's on my master. This is the way I plan to do it:

Failover to the slave (server B)
Change the HDD's on the master (server A) and reinstall it
Start the reinstalled server A as a slave
After server A has catched up with server B, change server A back into being the master, and then turn server B back into being slave again

(BTW. If you got suggestions to a better workflow please tell me)


texts: Create a foreign-key-like constraints that checks something more generic than equality

texts:
There are some situations where I would like to set up some constraints in a table that depend on values in another table.
For example, I can define two tables as:
CREATE TABLE foo (
    foo_id serial PRIMARY KEY,
    max_bar integer
);

CREATE TABLE bar (
    foo_id integer REFERENCES foo(foo_id),
    bar_id integer CHECK (bar_id >= 0),
    PRIMARY KEY (foo_id, bar_id)
);

I would like to go a little further to ensure that all bar.bar_id values are lower that the “corresponding” foo.max_bar.
Is there a way I can set up such a constraint?
To go a little further, I’ve found that an EXCLUDE constraint using a GiST index is a great way to set up UNIQUE-like (but more generic) constraints. For example I can define the following tables:
CREATE TABLE foo (
    foo_id integer NOT NULL,
    foo_validity tstzrange NOT NULL,
    EXCLUDE USING gist (foo_id WITH =, foo_validity WITH &&)
);

CREATE TABLE bar (
    foo_id integer NOT NULL,
    bar_validity tstzrange NOT NULL,
    EXCLUDE USING gist (foo_id WITH =, bar_validity WITH &&)
);

Then I would like each row in bar to reference a row in foo with foo.foo_id = bar.foo_id AND foo.foo_validity @> bar.bar_validity. It looks to me like it should be feasible with gist indices, but I can’t find a syntax to declare such a constraint…


texts: Database maintenance rebuilding indexes daily

texts:
My database is around 2.5TB. Is it a good practice to run rebuild indexes daily?

We delete data daily around 20 GB & at the same time we delete partitions & we will add new partition. To run rebuild index is taking 8 hours, sometimes 24 hours. During this, the log file is increasing to 300 GB.

We need to send daily reports for database fragmentation to customer. Seeing 99 or 98% they are scared. Please suggest.

Tables size is 70GB  & for this table fragmentation level daily it will be 99%. No idea what made customer request daily reports of fragmentation level, but he just need it daily.


texts: Connect to RDS from EC2 that uses Elastic IP. Access denied for user

texts:
SHORT:
I want to access my RDS MySQL from my EC2 instance. The EC2 has an associated Elastic IP address. I associated an Elastic IP address so the EC2 has a static IP.
I can create a MYSQL user my_user@orig_EC2_IP and access the RDS. However, I cannot connect when I create a user with the elastic IP: my_user@ELASTIC_IP. The instance seems to send along the original IP, not the elastic one.
I'd just use the my_user@orig_EC2_IP user, but this will stop working if the instance is restarted and changes IP address.
LONGER:
The following works:

Do NOT associate Elastic IP with EC2.
On RDS, add Security inbound rule, adding MySQL/Aurora access to EC2 IP Address.
Create a MySQL user:

grant usage on *.* to myuser@'<EC2 IP>' identified by 'password';
grant select on mydb.mytable to myuser@'<EC2 IP>';

SSH to the EC2.
I can access the RDS MySQL both as Admin and myuser.

However, I want to associate an Elastic IP to the EC2 so it has a static IP address.

Associate an Elastic IP address to the EC2.
Drop the myuser@'EC2 IP'
Do all the same as above, opening a port to the Elastic IP, creating a MySQL user @"elastic IP", SSH to EC2 using the elastic IP.

I can still connect from the EC2 with the MySQL admin user. I get an error when I try to connect to the RDS using myuser:
Access denied for user 'myuser'@'ORIGINAL IP'

It seems that it's sending along the original IP address, not the elastic one.


texts:
I currently have transactional replication set up for three databases from the same Publisher to the same three Subscribers in SQL Server 2005. Publisher and Distributor are on the same server.

One of the databases on one Subscriber stops for several hours each day at different times with no indication of why. No problem with all three databases on the other two Subscribers or the other two databases on the problem Subscriber.

Searching the system logs shows nothing abnormal happening on the Subscriber and Replication Monitor details just says "delivering replicated transactions". Inserting a tracer token shows the Publisher to Distributor is normal.

Any suggestions for troubleshooting this problem?


texts: Why does TokuDB say to remove innodb_buffer_pool_%?

texts:
The TokuDB quick start guide says "After converting your tables to TokuDB, you should comment out the global options related to memory caches (ie: innodb_buffer_pool%)."

If TokuDB supports both TokuDB and InnoDB database engines, why would I comment out these settings?


texts: MySQL Replication, Can i add a trigger to the SLAVE db?

texts: Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave.


texts: Is it a good idea to have an index when the column value changes several times over record lifetime?

texts:
I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're maybe three to seven such changes. Meanwhile I have thousands of queries that SELECT or UPDATE rows with specific State values. One rarely run UPDATE changes all rows that have 4 to 5.

I feel like adding an index on State column would be a good idea. Yet since State can change it means the index will have to be changed on every such change.

Do I add an index or not? How do I make a decision?


texts: Migrate legacy application to new db/schema

texts:
I apologize if I made any mistake/missing info, fist post here!

I am in the process of rewriting a website from scratch to a newer tech stack with a completely redesigned architecture. The website in question is a real estate website to rent houses for short period of times. It was designed/created in 1998-1999 and has been working ever since. A few cosmetic redesigns have been made to the website, but no changes have been made to the database or the architecture except for hosting-mandated updates to the infrastrcture (they run on one of those shared spaces). The current stack is as follows:

DB: MySQL (10.1.41-MariaDB-cll-lve)
Server: PHP 7.0
Client (Web): Simple Bootstrap website, nothing fancy

The system was originally designed by a student, which had no idea/experience with these kind of systems. The structure of the DB is quite flat, and there are just a few tables (< 5). There are almost no joins in the code, and as an example their booking periods are all put together in the same field, with the dates split by delimiters and concatenated in the format

DD:MM:YYYY-DD:MM:YYYY/DD:MM:YYYY-DD:MM:YYYY/...

So whenever they need to check for availability, they parse this string. Also, data types are scarcely used and most of the fields are strings.

I have rewritten the entire system with a new architecture and schema using relations whenever possible as well as adding quite a few tables. This is the stack:

DB: PostgreSQL
Server: Spring Boot (latest) with Spring Data
Client (Web): Vue.js

I now need to migrate the old data to the new system, and I am not sure how to proceed. I have thought of two different options:

Write a script that takes all the data from the DB, creates the relations and adapts to the new schema, and stores them in the new DB (EDIT: The script could also parse the dates, etc). Of course this needs to be properly tested but I am not in a time crunch. The script could be written in either Python (to allow me to manipulate the fields without having to deal with types) or using Java (being as I use Spring) and using the new models directly.
Extract the data from MySQL and dump it into PostgreSQL. Then, write some kind of an adapter that allows me to read the data and manipulate it little by little whenever needed. I am afraid of this option as Spring Data is quite strict at times and I would need to duplicate a lot of the models I have created.

Keep in mind the following things:

There are about 5000 entries between all the tables, so not a huge migration. Still, they have a lot of information that needs to be accurate and cannot be lost.
Spring Data complains quite easily about foreign key restrictions if you touch the schema in any way (i.e. manually) and therefore I am afraid of doing all the work and then having to deactivate safety features.
The migration will be done only once. Once verified, we will keep a copy of the old database just to be sure but everything else will be moved to the new system.

EDIT:
As pointed out in the comments, I forgot to ask the actual question :). Are any of the options I listed a good way to approach this problem? Are there any tools out there that may help me in this migration? I think that option 1 is the best for my case, am I missing something?

Any help is greatly appreciated!


texts:
I have an select:

SELECT
tb_dim_equipe.no_equipe "EQUIPE",
tb_dim_profissional.no_profissional "PROFISSIONAL",
no_cidadao "CIDADÃO",
a.nu_cns "CNS",
sum(case when co_dim_tempo >= 20190100 and co_dim_tempo <= 20200131 then 1 else 0 end) as "TOTAL"
from (
SELECT no_cidadao,
tb_fat_cad_individual.nu_cns,
tb_fat_cad_individual.co_dim_profissional,
tb_fat_cad_individual.co_dim_equipe from tb_fat_cidadao_pec
join tb_fat_cad_individual on tb_fat_cad_individual.nu_cns = tb_fat_cidadao_pec.nu_cns
join tb_fat_cidadao on tb_fat_cad_individual.co_seq_fat_cad_individual = tb_fat_cidadao.co_fat_cad_individual
where st_mudou = 0
and st_vivo = 1
and st_gestante = 1
and tb_fat_cidadao.co_dim_tempo_validade = 30001231) a

left join (
SELECT tb_fat_atendimento_individual.nu_cns,
tb_fat_atendimento_individual.co_dim_tempo
from tb_fat_atendimento_individual
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_atendimento_individual.co_dim_tempo
where co_seq_dim_tempo >= 20190100
and co_seq_dim_tempo <= 20200131
and ds_filtro_ciaps like '%ABP001%'

union SELECT tb_fat_proced_atend.nu_cns,
tb_fat_proced_atend.co_dim_tempo
from tb_fat_proced_atend
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_proced_atend.co_dim_tempo
where co_seq_dim_tempo >= 20190100
and co_seq_dim_tempo <= 20200131
and ds_filtro_procedimento like '%0301010110%') b
on a.nu_cns = b.nu_cns
join tb_dim_equipe on tb_dim_equipe.co_seq_dim_equipe = a.co_dim_equipe
join tb_dim_profissional on tb_dim_profissional.co_seq_dim_profissional = a.co_dim_profissional
group by no_equipe, no_profissional, no_cidadao, a.nu_cns
order by no_equipe, no_profissional, no_cidadao

This return to me five columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL

I want to create a new select command like this:

SELECT sum(c.TOTAL) from *query above* c

but I receive the error that column not exists.

How I can use a select with the names of alias columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL?


texts: MySQL data warehouse calculate difference over years in percentage

texts:
I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months.

My question is how to add the new column to calculate the differences (positive or negative) against same month from base year.

I'm aware they are two questions in one but if the above question is not possible, it is possible to compare a given date range against current year/month.
Please see the below table for a better overview of the question. This is the desired output..

The simple SELECT statement (without COUNT or SUM) is like this:

SELECT
  dim_date.year,
  dim_date.month,
  fact.total_cost
FROM fact
  INNER JOIN dim_date
    ON fact.date_key = dim_date.date_key
WHERE dim_date.year BETWEEN 2009 AND 2011

For simplicity please have a look at below star schema.

Thank you in advance for any help.


texts: How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?

texts:
When I create a table like this

create table char_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go

I can store varchars containing ≥

insert into char_test values ('≥');

When I do a

select item from  char_test;

I get

item
----------
=

But using

select replace(item, '≥', '>=') from  char_test;

gives

-----------------
>=

So the database knows the character. How can I convert the string to a nvarchar showing this character?

Edit:

The important fact here is, that SQL Server here maps some different characters to the same character without throwing errors or warnings.

I dare call this very bad design.


texts: Schedule Full, Differential and Transactional Backup in SQL Server 2016

texts:
I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent).

My first concern is, what is the best way to schedule my backups?

I want full backup every day, differential backup every hour and transactional log backup every 15 mins.

My second concern is, is this a good practice?

I noticed an issue when I used SQL Server Agent. My full and differential backups are overwritten. I am ok with having the full backup overwritten but not for the differential since when I need to recover it, I would only have one differential backup which is not the purpose of differential backups.

How can I not let this overwrite on previous backups?

The last concern and question is regarding the way to implement the backup scheduling. I am going to use Maintenance Plans , and may schedule three different backups. One for full, one for differential and one for transnational.

Is this the best practice for a database that is used everyday by users? (at least 2000 transactions per day)


texts: SQL Query- field calculation based on date

texts:
I'm writing a query for sales targets per week, however I'm getting a little stuck on this.
Targets are per month, per year, so for example, Brand A has a monthly target (changes each month)

The snippet from the report is as follows:

SELECT BrandName
      ,SUM((TargetMonThur *4) +TargetFriday)
WHERE [MONTH] IN (
        DATEPART(MM,@DAY1),
        DATEPART(MM,@DAY2),
        DATEPART(MM,@DAY3),
        DATEPART(MM,@DAY4),
        DATEPART(MM,@DAY5)
    )
    AND [YEAR] IN (
        DATEPART(YY,@DAY1),
        DATEPART(YY,@DAY2),
        DATEPART(YY,@DAY3),
        DATEPART(YY,@DAY4),
        DATEPART(YY,@DAY5)
    )

However the issue appears when the start of the week/ end of the week is a different month to the other days. at this point, it calculates both month's targets * 4.

I think I'm being a complete noob on this, but any help would be great.

Thanks in advance


texts: how to setup percona pmm monitoring of percona 5.7 in docker container

texts:
I want to monitor percona running in docker with the percona monitoring and management client.

I have percona monitoring and management installed, running and monitoring other percona instances (https://www.percona.com/doc/percona-monitoring-and-management/deploy/index.html). I recently added percona (currently version 5.7) running in docker container (pulled from https://github.com/docker-library/percona).

I found an open feature request to add the pmm-admin executable from inside PMM Server at https://jira.percona.com/browse/PMM-627

Is there way to setup to monitor my percona instance running docker?


texts: MySQL Help with Desktop Based Administration

texts: I am making a MySQL based database for a professor and she would like to have a version uploaded to the web and a version on her desktop for personal use.  The web version would be "official" whereas the one on her desktop would be used for personal research and would have additional categories, etc.  The two databases do not have to be linked but I was unsure if it would be easier to put both databases on the web and make her personal one password protected or if there was a good desktop based program.  She is not very tech-savvy which is why I was leaning away from having her install MySQL and Apache on her own desktop.  Any help would be appreciated.


texts: Why my heap read/heap hit ratio is negative?

texts:
I have a database with 200k rows (one of this column is a jsonb data type column), i'm using this database to store some various devices informations (currents, pressure, etc...) and i'm using this table to get the respective measurements of a specific device using a time range. When the database is small (like 50/70k rows) everything works fine, the cache hit ratio is positive and around the 99%.

When the database grows, like now, the cache hit ratio will decrease dramaticaly and goes to negative, as shown below:

But, as you can see, the index on the table it seems to be in a good state (despite the low number of rows contained in the index):

The database size is around 200MB and the modbus table has the size of 156Mb, my host has a 1GB Ram with 2CPU.

Here there is one of the slowest query, for only 54/60 results, it gives 10 seconds of execution:

Please, give me some advices...

Thanks


texts:
I have two tables "using" foreign keys:

Referencing table

CREATE TABLE `requests` (
`request_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
--
`status` enum('new','approved') DEFAULT NULL,
--       ^^^^
PRIMARY KEY (`request_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Referenced table

CREATE TABLE `status` (
`status_id` int(11) NOT NULL AUTO_INCREMENT,
`status_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`status_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now, I want something like this, If I create a new row, say, 'pending' name in the status table then status (list of ENUM values) of requests table automatically updated with 'pending' ENUM value. So, the "final" status allowed ENUM values in the request table would be:

status('new','approved','Pending')


texts: How do I collapse schemas in SQL Server Management Studio?

texts:
I am not sure if this is possible, but I am hoping it is - I am currently running SSMS and have a database that has it's data all "housed" in the DBO schema.  I have received advice (and completely agree) that best practice will be to create additional schema's that match our business functions, and I will be moving ahead with that.

I would like to know if there is any way - even if it is by install an add-in of some kind, to have the schema collapse so that I can have all the DBO, and RPT objects hidden until I expand the schema - much like the tables and other folders are collapsed.

I apologise if this question is not quite "on topic" for this site - but I think this is where the answer is most likely to be known.

In my screenshot below - I am looking for the bit outlined in red to be collapsedand there just to be 2 lines the DBO that is there now - and the RPT which I currently have the request in to create:


texts:
I have a question to understand better how NUMA nodes help in SQL Server:

In our current set up, we have 4 NUMA nodes with 20 cores and hyper threading enabled, making a total of 40 logical processors. With memory of 400 GB, each NUMA gets 100 GB each.

It is a physical server with SQL Server 2012 Enterprise (core based licensing) on Windows 2012 R2 Standard. It has 4 sockets with 10 cores per socket and 20 logical processors per socket total 80 from error log what I see.

My understanding is maybe having 4 NUMA nodes might not perform better if we move (future SQL refresh to 2017/19) with same number of NUMA rather than I prefer 2 NUMA.

Why I am saying this because I think with 2 NUMA 400 GB will be allocated as 200 GB in each.

Therefore my understanding is it might perform better or its best to have this configuration based on how queries are switching between nodes for memory. Yes we are seeing some memory issues, I know there are other related issues which we are working but this question is more specific to NUMA set up for SQL Server.

Could someone please share their experience or may be provide more insight as I might be thinking wrong here with the belief query will have to less swapping here and there on 2 NUMA in comparison with 4 NUMA?

Is there a way or a query I can check if queries are spending time in swapping over two different NUMAs for memory and can be an issue?

Is there a way we can measure if two nodes might work better than four nodes for future design (cross-node memory access)?


texts: is it possible to make multiple mongo routers connected to just one shard on a mongoDB shard cluster?

texts:
I'd like to make a mongo cluster with multiple mongo routers(mongos) with just one shard(mongod) like thses figures.

So I made two mongo routers named 'mongorouter-1', 'mongorouter-2',
and also made one shard named 'mongod'.
Two config servers were made and connected to each mongo routers like the first figure(not like the second figure).
These were made as Docker container with the official Mongo Image
and these were on the same network so they are connected to each other.

In mongorouter-1 I added 'mongod' well with this command.
sh.addShard("mongod:27017")
It works well, but In mongorouter-2 this command put an error, like

mongos> sh.addShard("mongod:27017")
{
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: admin.system.version index: _id_ dup key: { : \"shardIdentity\" }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "operationTime" : Timestamp(1558591937, 1),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1558591937, 1),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

In mongorouter-1, sh.status is this

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5ce6683cc490bfc9325389cb")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "mongod:27017",  "state" : 1 }
  active mongoses:
        "4.0.6" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }

and in mongorouter-2, sh.status is this

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5ce668176a4dcc52fd230ac9")
  }
  shards:
  active mongoses:
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }

I don't know how to make multiple mongo routers connected to just one shard like the figures.
If you know the solution, help me. Thanks in advance.


texts: SQL Server 2012 Availability Group is it "AlwaysON"?

texts:
In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to failover cluster instance.

Does AlwaysON Availability Group mitigate this problem? Is failover in case of SQL Server 2012 AlwaysON availability group transparent to clients connecting to the SQL Server?


texts:
I occasionally see questions asking how to safely store user passwords for a web application (using an RDBMS, I'm not talking of Facebook or Twitter). The usual answer is "salt the password, then hash it with a strong algorithm such as TDES or SHA512".

My question is : As an RDBMS user, why should I bother at all with the password storing problematic at all since most engines have a built-in authentication mechanism.

For example, if some user X wants to create an account user password Y on my web application, how is issuing the following query wrong:

CREATE USER X WITH ENCRYPTED PASSWORD Y IN GROUP baseuser;

Then within my application, the user can open a connection to the database using his credentials and I don't have to bother at all of password management.

I see multiple advantages to this method:

If the RDBMS decides that the encryption algorithm needs to be changed, I don't need to touch anything, just to apply the security updates;
It is easy for me to manage the users authorizations. If a user is promoted to an administrator's role, I just have to add the user to the corresponding group;
SQL injections are now meaningless, for I manage permissions to allow exactly what I want to allow to each user in the database (for example, in a forum like SO, adding new posts, answering to posts, commenting and editing/deleting his own questions/answers/comments);
An user account "anonymous" can be used for unauthenticated connections to my application;
Each user is the owner of the data he provided.

But on virtually every question I see on this topic, there seems to be a general consensus that this is not the way things have to be done. My question is : why?

Note : The third point is allowed by policies in PostgreSQL, and security policies in Microsoft SQL Server. I realize that these concepts are newcomers, but anyway, now that they are here, why doesn't the technique I describe become the standard way to handle users accounts?


texts: Move multiple databases from C: to D: at once

texts:
I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD.

Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive.

I have found this MSDN article, but that seems to be the procedure to move only one database.

Is there an automatic way or script to move all my databases at once?


texts: Users complain that system runs slow when mysqldump is in progress

texts:
The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump --skip-opt --quick --single-transaction --create-options --extended-insert --disable-keys --add-drop-table --complete-insert --set-charset -- compress --log-error=Proddb0635.err -u root -pjohndoe Proddb> \devNas\devNas\sqlbackup\LIVE\db\Proddb0635.sql

The backup file Proddb0635.sql is stored on a separate server from the database server.
RAM is 12 GB.
INNODB Buffer Pool size is 6 GB.
Additional mem.pool is 32 MB.
Query Cache size is 2 GB
Net buffer length is 16 M
Max. packet size 1 GB.

mysql version is 5.0.67.

When backup is not running users are happy with the performance.

When backup is running INNODB Buffer pool hit rate is high close to 100%.
There are no pending reads or pending writes. innodb wait free is 0.
CPU usage is not high min 9% to max 15 %
Query cache hit rate is low about 40% with or without mysqlbackup running. Currently Windows Task Manager  is displaying that 10GB of RAM is being used. Should I increase Query Cache with only 2GB of RAM available?
mysqlld-nt is taking 9.2 GB of RAM and mysqldump is taking 5 MB of RAM.
Alos, noted that the size of the dump file is same in presence or absence of --compress option.

SHould I decrease the iNNODB Buffer Pool size?

Thanks


texts: Reading 300% rows - Problematic execution plan

texts:
Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4

I am struggling with a query that uses a lot of time.
The obvious reason for this is wrong estimates of amount of rows.
I have tried indexing and updated stats, however I do not seem to address the real problem since the query still goes amazingly slow (I guess wrong index or stats).

What I need are help with interpreting this information so I can help the query to be optimized. What index, which DBCC functions or other built in functions should I run between executions to ensure clean cache and whatnot to avoid using wrong stats and  the fresh new one?

Examples would be

DBCC FREEPROCCACHE
UPDATE STATISTICS *table*

Another bottleneck of the same query would be these hash matches. The Hash Match at the right most are finished within seconds, while the one to the left seems to struggle with the last 213 rows that uses many minutes. What can I do to find out where the problem lies in these hash matches?

I am also trying to solve multiple memory spills in our batch jobs, where I can only seem to optimize memory spill from a single table.

I have multiple sorts and hash matches where there are rather long probes and residuals including multiple tables or "Expressions" which I assume are either aggregated or set by SSIS packages.

Should I solve the 'first' or 'last' spills first? First are 'top of the three' and last would be closest to leaf nodes (operators). I do also wonder about some operators, mentioned below.

Can you explain what the terms mean regarding the execution plan descriptions:

Build residual
Probe residual
Hash keys probe

I believe I understand the following  terms:

Order by: The order of which the operator needs the data in

Output list: Which data the operator are retrieving for output


texts: Feature SQL Server 2012 - Filetable - The location of the file can not be opened

texts:
When trying to access the files available on the virtual directory created for FileTable, you receive the following error message:

  "The location of the file can not be opened. Access is not enabled or not you have the permissions for it."

Already granted all permissions, however, the application displays this error.


texts:
I have two servers one is application server and another is database server. Continuously I am firing query to mysql. At peak time I get error -

Can\'t connect to MySQL server on \'IP Address\' (99)

I couldn't track the issue.

Below is my my.cnf file-

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-time-zone = '+05:30'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,

innodb_file_per_table=1
skip-name-resolve
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 4096M

log-error=/var/log/mysql/mysql_error.log
long_query_time=2
slow_query_log=1
log-warnings=2
slow-query-log-file=/var/log/mysql/log-slow-queries.log
max_connections = 500
wait_timeout=10
interactive_timeout=10
general_log_file=/var/log/mysql/general.log
max_allowed_packet=256M
#log_queries_not_using_indexes = 1

#starting
#old_passwords=1

binlog-format=MIXED

server-id=1
#ending
key_buffer_size = 2048M

thread_cache_size = 128M

table_cache = 75000
table_definition_cache = 40000
open_files_limit= 75000

sort_buffer_size = 3M
read_buffer_size = 5M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 20M
join_buffer_size=5M

query_cache_limit = 100M
query_cache_size = 2048M
query_cache_type=1

tmp_table_size = 256M
max_heap_table_size = 256M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log-warnings=2

At a particular time around 7000 tcp connections in TIME_WAIT and 1 or 2 in ESTABLISHED from application server to database server.

No server load, No Memory Issue, No firewall on both the server.

Please help me to solve this issue.


texts: Having the backup file, how to work out the total disk space required to do the restore?

texts:
I have a nice query that gives me the date of last full backup, size and location, even when I backup to multiple backup files.

what I don't have is an estimate of the total backup size (for each of the DB files).

Basically, how much disk space in each drive I will need in order to do this restore?

I have been trying with

RESTORE FILELISTONLY FROM DISK=N'C:\SQL Server\BACKUP\my_backup_FULL_20190324_190109.bak'

but I could not work out the total disk space required for the restored-to-be database.

I got the size from the restore filelistonly above however, the size of the original db in disk is the following:

I just found out:


texts: How to optimize storage of sales details with multiple products in each sale in Microsoft AccessDb

texts:
I have created an accdb with customers and products tables.
Now, I want to know how to store the sales order details in the Sales table, WHEN each sale has multiple products, each with different quantity and price.

Like Order S01 -> Customer X -> Products a, b, c -> qty 1, 2, 3 -> price 100, 200, 300

I am planning to insert and update sales details from my excel sheet.

As my order number will be the unique primary key, I think its illogical to repeat order numbers for each product.

Like,

S01 | X | a | 1 | 100

S01 | X | b | 2 | 200

...

Currently, I have implemented this as comma-separated values in a field, but it seems it is not a good practice to store like this. It involves lots of unnecessary scripting to unwrap and wrap the data and not at all intuitive.

Like,

S01 | X | a,b,c | 1,2,3 | 100,200,300

I cannot create a separate table for each product to store the sales and purchase data(if this is called normalization). Too many tables, not good practice.

Like,

Table a

S01| X | 1 | 100

Table b

S01| X | 2 | 200

...

So, whats the most optimized and intuitive way to store such data and retrieve it.


texts: SSAS How to calculate Standard Deviation

texts:
I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average.

The data comes from many different "Data sources". These are are held in a Dimension.

I tried using STDDEVP function as shown in this example which operates on a measure.

But the results were wrong. I assume it's wrong because it's working on totals rather than raw data. [Sum of Values] is a standard aggregate measure summing the data in the fact table.

Following the link supplied by Greg I am now using this:

CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation]
 AS  (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5),
FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact TS Data';

Which gives me this:

Which still isn't correct.

Using SQL If I calculate STDDEV for Air Temp with the following query:

  SELECT stdevp([Value])
  FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries]
  where [DETL_DataSourceSurrogateKey] = 78

I get: 6.13770334742149

How do to get the MDX query to calculate a STDDEV per Datasource in my Dimension?

I've also tried using a Measure with "No aggregation" but it didn't return anything.


texts: SQL Server database generate script how to avoid adding PRINT statements

texts:
I need to create Insert scripts from the content of a table. To do that, I used the database tasks -> generate scripts.

The table is quite big and in the generated script, it adds PRINT command after each 100 number of rows and a GO statement. For example:

print 'Processed 9200 total records'
GO

For some reasons I can't have GO and PRINT command in the script, repeated in the middle. Is there any way to avoid creating them in the generated script? It is really time consuming to go one by one and delete all PRINT xxxx statements.

Thanks.


texts: Force Postgres to write log to a new file

texts:
I've updated the value of log_line_prefix, and I wanted that the log file will contain only logs with the new prefix.

I deleted the log file (postgresql-9.3-main.log), assuming that once the file is gone, postgres will just create a new one, but that is not the case, and now the is no log file.

I read somewhere that the server still holds the fp, and this is why it doesn't create a new file.

Restarting the service will probably solve the issue, but I can't do that now.
Is there a way to tell the service to create a new log file?


texts: I am unable to connect to mongo its show the below error


texts:
I have created a procedure as

create procedure testPagination
(
@PageNumber bigint,
@PageSize Bigint,
@OrderByColumn varchar(1000),
@OrderType varchar(1000)
)
As
Begin
    --print @PageNumber
    --print @PageSize

    declare @strSQL nvarchar(max)
    declare @intStartSrNo bigint
    declare @intEndSrNo bigint

    if @OrderType = 'asc'
    begin
        set @intStartSrNo=((@PageNumber*@PageSize)-@PageSize)
        set @intEndSrNo=(@PageNumber*@PageSize)
    end
    else if @OrderType = 'desc'
    begin
        create table #RecCount(cnt bigint)
        declare @intTotRecords bigint
        set @strSQL='insert into #RecCount(cnt) select isnull(max(a.sr_no),0) from ' + @OrderByColumn + ' a with(nolock)'
        exec sp_executesql @strSQL
        select @intTotRecords=cnt from #RecCount

        set @intStartSrNo=(@intTotRecords-(@PageNumber*@PageSize))+1
        set @intEndSrNo=((@intStartSrNo-1)+@PageSize)

    end
    else
    begin
        return
    end

    if @OrderType = 'asc'
    begin
        set @strSQL=
        '
        select a.sr_no, bv.id,bv.name_voter,bv.home_street_address_1, bv.home_address_city from ' + @OrderByColumn + ' a with(nolock) ' +
        ' inner join base_voter bv with(nolock) on bv.id=a.base_voter_id where a.sr_no > ' + cast(@intStartSrNo as varchar(500)) + ' and a.sr_no <= ' + cast(@intEndSrNo as varchar(500))
    end
    else if @OrderType = 'desc'
    begin
        set @strSQL=
        '
        select a.sr_no, bv.id,bv.name_voter,bv.home_street_address_1, bv.home_address_city from ' + @OrderByColumn + ' a with(nolock) ' +
        ' inner join base_voter bv with(nolock) on bv.id=a.base_voter_id where a.sr_no >= ' + cast(@intStartSrNo as varchar(500)) + ' and a.sr_no <= ' + cast(@intEndSrNo as varchar(500))
    end
    --print @strSQL
    exec sp_executesql @strSQL
    return

End
go

3 indexes as

 create index IX_base_voter_name_voter_asc_deleted_at on base_voter(name_voter asc,deleted_at) include(home_street_address_1, home_address_city) where deleted_at is null
 create index IX_base_voter_home_street_address_1_asc_deleted_at on base_voter(home_street_address_1 asc,deleted_at) include(name_voter, home_address_city) where deleted_at is null
 create index IX_base_voter_home_address_city_asc_deleted_at on base_voter(home_address_city asc,deleted_at) include(name_voter, home_street_address_1) where deleted_at is null

Now, when I try to execute this stored procedure I receive the error

Invalid Object name 'home_address_city'

I am executing by right-clicking on the stored procedure, which actually executes the following query

USE [test_db]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[testPagination]
        @PageNumber = 1,
        @PageSize = 50,
        @OrderByColumn = N'home_address_city',
        @OrderType = N'asc'

SELECT  'Return Value' = @return_value

GO

I have already hit ctrl+shift+r a lot but with no success


texts:
Currently these updates are available for SQL Server 2014:

SQL Server 2014 SP1 CU2
SQL Server 2014 SP1 CU1
SQL Server 2014 SP1

If I want to do a slipstream install:

Is it enough to download #1 only and do the slipstream install with it?

Or should I download #3, then do the slipstream install, then apply #1?


texts:
I have some groups of two tables, each group table having the same structure. (dev & prod server)
Their data will never change from user action, only from admin when a change occurs.

I want to make sure that data inserted in my fields is identical from dev to prod, without noise like server version, timestamps of anykind, autoincrements...

I made a little query using md5 and group_concat, instead of checksum, because I need to skip some fields, and also I was not sure about how deep is checsum (like, does it compare also auto-increment state..? etc...)

Then I saw that group_concat has a default character limit of 1400.
Some Text fields contains very long strings (about 200 000 characters).

Increasing this limit from 1400 to more than 200 000 sounds a bit like a bad idea, but I would like your advice considering this :

My actual query is : SELECT md5(GROUP_CONCAT(CONCAT_WS("", '.$fields.'))) FROM '.$table
I'm looping this query on about 20 tables
tables can have from 3 to 20 fields of any kind (tinyint, text, date) and any length

So :

What do you think about this query + a big fat SET SESSION group_concat_max_len = 900000; ?
Do you have an alternative to checksum regarding my doubts ?


texts: Oracle 12c, can not chose standard edition when install!

texts:
i'm trying to install Oracle 12c on Windows 2012 R2 ... I get Enterprise Edition option only!

I need to install Standard Edition!


texts: How to atomically replace table data in PostgreSQL

texts:
I want to replace the entire contents of a table, without affecting any incoming SELECT statements during the process.

The use case is to have a table which stores mailbox information that is regularly extracted, and needs to be stored in a PostgreSQL table. There are many clients using an application that is constantly querying that same table.

Normally, I would do something like (pseudocode incoming)...

BEGIN TRANSACTION
TRUNCATE TABLE
INSERT INTO
COMMIT

But unfortunately the table cannot be read during this process; due to the time it takes INSERT INTO to complete. The table is locked.

In MySQL, I would have used their atomic RENAME TABLE command to avoid these issues...

CREATE TABLE table_new LIKE table;
INSERT INTO table_new;
RENAME TABLE table TO table_old, table_new TO table; *atomic operation*
DROP TABLE table_old;

How could I achieve this in PostgreSQL?

For the purposes of this question, you can assume I am not using foreign keys.


texts:
We recently moved from SQL Server 2012 (SP3) to SQL Server 2017 via restored backup.  When we were running on 2012, performance was adequate.  Since moving to 2017 we have noticed a degradation in performance.

Digging in has shown that this may be a Write Log speed issue.  As I was trying to find more information on that I found this query for spinlocks: https://www.sqlskills.com/blogs/paul/capturing-spinlock-statistics-for-a-period-of-time/

Running it shows most of my values in the low 100's.  But one value far exceeded all the others.  SOS_CACHESTORE_CLOCK had a value of 2,875,000 Diff Spins.  I have no idea what this is, but having it be 2,000 times larger than all the others made me worry.

Googling has shown only that this value is for "internal use only".

I am asking here in hopes that someone has a hint on what this is for any why it is so high.

Note: The value is SOS_CACHESTORE_CLOCK, not SOS_CACHESTORE.


texts: In SSIS 2012, what is the purpose of flagging a package as an "Entry Point" package

texts:
In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package"

Doing a search I found this page on MSDN which states:

  The value of 1 signifies that the package is meant to be started
  directly. The value of 0 signifies that the package is meant to be
  started by another package with the Execute Package task. The default
  value is 1.

With this flag enabled and disabled I have been able to execute a package directly.

What is the purpose of enabling or disabling this flag?  Is it merely to document the intentions of your own SSIS packages or does SQL Server/SSIS behave differently when it enabled or disabled?


texts: Convert SQL server stored procedures to postgreSQL

texts:
I have a SQL Server database that I am migrating to PostgreSQL 12.0.
I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, which also need to be translated to PostgreSQL.
Unfortunately, the tool I was using doesn't convert stored procedures. Any ideas on how I should go about this or is there a tool which can do this?


texts: ORA-01031: insufficient privileges connecting to standby

texts:
I configured a standby database and it worked for a while, but now I noticed that the redo logs thread on standby is far behind the primary. After researching alert log and trace files, I figured out that there is a problem with connection to the standby server.

When I type on the primary server:

sqlplus sys/manager@TESTSTAN as sysdba

The output is:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 29 13:36:17 2016

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

ERROR: ORA-01031: insufficient privileges

But I am able to connect from the standby to the primary:

[oravis@standbysrv bin]$ sqlplus sys/manager@TESTPROD as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 29 13:46:18 2016

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size          1336960 bytes
Variable Size         335546752 bytes
Database Buffers       79691776 bytes
Redo Buffers            6094848 bytes
Database mounted.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
testprod     MOUNTED

Password file the same on both hosts. Also, I've recreated it for a few times and this didn't help. remote_login_passwordfile parameter is exclusive on both servers.

listener.ora on the standby

    SID_LIST_LISTENER =
    (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = teststan)
        (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1)
        (SID_NAME = test) ) )

    LISTENER =
     (DESCRIPTION_LIST =
     (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521))
     ) )
    DYNAMIC_REGISTRATION_LISTENER=off

tnsnames.ora on the primary:

TESTSTAN =
 (DESCRIPTION =
 (ADDRESS_LIST = (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = standbysrv)
         (PORT = 1521)) )
 (CONNECT_DATA = (SERVICE_NAME = teststan) )
 )

TESTPROD =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = oratest)
         (PORT = 1521)) )
 (CONNECT_DATA =
         (SID = test)
         (SERVICE_NAME = testprod)
 ) )


texts:
I am wondering if I can trust my host doing it's nightly snapshots with live Innodb tables.
From what I have read:
"Innodb is ACID-compliant, so you can "safely" use fs snapshot with Innodb tables."

Is this true?
Can you safely snapshot Mysql instances with large Innodb tables and have no ill effects?
Is this documented anywhere?


texts: query to replace text with sequential numbers

texts:
I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table.  what I need is to replace certain text in the "description" with numbers respectively.

Example of the "description" entry to all items:

"Photo number # more text text"

The query should replace the "#" symbol with sequential numbers, ordered by 'name' column.  Where the first row should be Photo number 1, and second Photo number 2.. etc.

I tried some queries with row_number (in phpmyadmin), but I am not sure if it is the right way to do that or what..  as they didn't work.

Server version: 5.7.24-0ubuntu0.16.04.1 - (Ubuntu)


texts: Select rows based on latest date with multiple joins

texts:
I have this query (SQLFiddle):

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name

FROM company c

LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id

WHERE u.user_name='Morgan'

-- WHERE c.name='Fiddle'

GROUP BY c.id

HAVING a.rtime IS NULL OR a.rtime = (
 SELECT max(rtime)
 FROM action a2
 WHERE deleted IS NULL
 AND a2.company_id = c.id
 )

Problem 1

I want to list all the companies, and show the user and status where they last made an action on the company. At the same time show the companies where no actions have been made.

Problem 2

I also need to be able to search for the user by name, thereby selecting all companies were this user had the last activity. The query is made from a form, so I can inject variables.

I am not able to change the database SCHEMA at the moment, but advices for a future migration is much appretiated.

I've tried binding it together with INNER JOIN ( SELECT.. ) t ON but I can't get my head around it.

I've also tried methods from
here, here, and here but I can't get the person with the latest activity right.

MySQL version: 5.5.16.
The company table has about 1mill rows, and the action table is at 70K, growing. Performance is important to me here.

How can this be solved?


texts: Which picture does the ORA-01830 error message refer to?

texts:
ORA-01830: date format picture ends before converting entire input string

About the error message.

I am confused. Which picture is the error message talking about? All I provided was a date.


texts: can I select columns based on their name in Mysql?

texts:
is there a way to select columns using '%' or 'like'?
for example:
I have a table with 20 columns , and 4 of them are starting with "GPS_"(GPS_long,GPS_Lat,GPS_sat,GPS_Speed)
I want to run something like this:( I know this is NOT the way...)
SELECT like '%GPS_%' FROM sample.table

so it will bring only the GPS columns  ?
Thanks ,


texts: Question about composite indexes and WHERE + GROUP BY

texts:
Alrighty so I understand how composite indexes work with WHERE but what if a column in the middle of the index is in the GROUP BY, as follows:
CREATE TABLE `test` (
      `a` VARCHAR(255) NOT NULL,
      `b` VARCHAR(255) NOT NULL,
      `c` VARCHAR(255) NOT NULL,
      INDEX(`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

SELECT * FROM test WHERE a = ? AND c = ? GROUP BY b

Would MySQL stop using the index before c, or would it be able to use all three indexes in the above query since b is in the GROUP BY.  I haven't been able to find any resources on how exactly GROUP BY utilizes a composite index in a case like the above.  In this case I'm using MySQL for database flavor.


texts:
We currently have a 4 Node cluster used for our Availability Groups.  2 nodes in local data center and 2 nodes in remote data center.

Currently the 2 nodes local are Synchronous and the 2 remote nodes are ASynchronous.

They are setup in a multi-subnet network.

We are wanting to make one of the remote nodes Synchronous so that we can manually failover to the remote data center to run production traffic.

Our testing turned up a network issue that we did not think about.

We use DNS aliases to connect to the listener. (ex. product.company.com) that resolves to the primary through the listener IP address. When we failover manually to the remote node the listener IP stays in the local subnet IP.

My question is how can we configure our DNS Alias to automatically resolve to the remote listener to hit the remote as primary with either CNAME or other DNS options.

Below is a crude diagram I tried to work up.


texts: Multiple databases or tables with prefix?

texts:
I was creating an Accounting ERP for a multibrach company.Normally i would keep different databases for different branches, Can anyone tell me which is the standard way?
1) create databases respective to branches
2) keep single database and arrange tables with branch prefix


texts: SQL*Loader-926, ORA-04031 unable to allocate x bytes of shared memory

texts:
A customer received this error while importing serveral files. The import routine (VB.NET) is using SQL*Loader (Release 11.2.0.3.0) via Process on each table separately (64 in total) and is called weekly for many years now.
Last week following error was thrown:

value used for ROWS parameter changed from 1024 to 795 (all tables are imported with ROWS=1024, no BINDARRAY parameter specified)

  SQL*Loader-926: OCI error while executing delete/truncate (due to
  REPLACE/TRUNCATE keyword) for table x ORA-04031: unable to allocate
  4160 bytes of shared memory ("shared pool","unknown object","sga
  heap(1,0)","modification ")

I'd googled the ORA-4031 and determined the current memory informations:

3,08 GB (shared pool) (select sum(bytes), pool from v$sgastat GROUP BY pool),
922MB free memory (select * from v$sgastat where name = 'free memory';) and
1,18 GB shareable memory (select sum(SHARABLE_MEM) from v$db_object_cache;)

To resolve the issue several posts pointed out to look at the views v$SGA_TARGET, v$PGA_TARGET, v$SGA_TARGET_ADVICE, v$PGA_TARGET_ADVICE, v$SGA_DYNAMIC_COMPONENTS and so on, but i have no dba privilege. (table or view does not exist).

Thus i'd like to know whether i can resolve the issue only with SQL*Loader settings or must a DBA investigate into this?

EDIT:
control file: LOAD DATA INFILE '<filepath>' INTO TABLE x TRUNCATE(<columns>)
call: sqlldr.exe userid=... log=... ctl=... bad=... rows=1024


texts: why does my clustered index scan have ordered = false

texts:
When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False.  I will mention that we have parallelism involved, though it does not appear to have any relation to these.  The indexes were set up with ordering.

What does this mean?  That the query plan is ignoring this order?  Or that the query is choosing not to order?  Or... heck, I have no idea what I'm looking at...!


texts: The number of records that is the table shows in .dbf file is not match with the real number of records

texts: I have some .dbf files made in VFP. I have to work with them and occasionally edit it. Recently I noticed that one of .dbf`s is running in a wrong way. The number of records that is the table shows is not match with the real number of records. I have tried to modify the header of this table to reflect an estimated number of records but with no result. How can I get my origin files back?


texts: Why does SQL Server truncate my nvarchar(max) string in an expression?

texts:
It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable.

An example is easier to explian:

DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing';
DECLARE @MySrc AS NVARCHAR(6) = '';

SELECT @MyDest = ISNULL(NULLIF(@MySrc, N''), @MyDest);

SELECT @MyDest;

The result is 'Some l' while I would expect 'Some long text, for testing'.

This is a simplified version of a more complicated bug I'm trying to fix. Is this a bug in SQL server or should I be doing this differently?


texts: How to use DMBS_LDAP to select 3 attributes for selected entries

texts:
I'm attempting to use DBMS_LDAP to create a procedure in Oracle 11.2.0.2.0 that ultimately updates an employee table from active directory.

The attributes I want are sAMAccountName, GivenName, and sn.

The variable that is used so store the searched attributes is:

l_attrs        DBMS_LDAP.string_collection;

so far I've seen people use:

l_attrs(1) := '*';

&

l_attrs(1) := 'sAMAccountName';

This allows me to get all or 1 attributes for a given entry but I certainly don't want all the attributes.

My question is, is there a way to specify multiple attributes?

Thanks,


texts: rh-postgresql96(Red Hat provided postgresql) vs postgresql-9.6 (community postgresql)

texts:
Our client has provided us Red Hat machine for setting up PostgreSQL. We are allowed to install red hat provided packages only.

So we have to install red hat provided postgresql (rh-postgresql96) instead of postgresql-9.6.

Is it a good approach to go with red hat specific PostgreSQL in production instead of community PostgreSQL?

Also, we are trying to set up repmgr for this PostgreSQL in the local environment but we didn't find any suitable package for this PostgreSQL.

How can we install any high availability solution like repmgr for Red Hat provided PostgreSQL?

any help would be appreciated.


texts: Fetch data from SQL Server table of the ERP DB

texts:
I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of legacy systems as well. Some of them are VBA frontend and stored procedures as backend and business logic. Some of them are C# applications. I know it is a very bad approach and I'm looking for better solution. I fetch data directly from ERP DB with SELECT WITH (NOLOCK).

If one day (hopefully soon) I will have to replace my ancient ERP with a new one, it will be a nightmare to update thousands of stored procedures, VBA and C# code.

The only solution I can think about is a definition of objects. For example, I plan to maintain P/N table which will hold all my part numbers and relevant data. On another hand, I have to merge the data from ERP to the P/N table every 5 minutes (either SSIS or stored procedure) to keep it updated. It is a huge load on my ERP DB. Considering tremendous amount of objects (warehouses, inventory data, logistics and financial transactions, engineering data) this approach will kill my ERP DB.

Can you propose a better solution?


texts: What techniques can I use to debug TSQL code now the debugger in SSMS 18 has been deprecated/removed?


texts:
I found some old files in the mysql datadir:

-rw-rw----  1 mysql mysql     0 2012-10-09 09:39 #sql_263c_0.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:39 #sql_263c_0.MYI
-rw-rw----  1 mysql mysql     0 2012-10-09 09:40 #sql_263c_1.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:40 #sql_263c_1.MYI
-rw-rw----  1 mysql mysql     0 2012-10-09 09:40 #sql_263c_3.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:41 #sql_263c_3.MYI
-rw-rw----  1 mysql mysql  338M 2012-08-21 15:54 #sql_3e03_0.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-08-21 15:52 #sql_3e03_0.MYI
-rw-rw----  1 mysql mysql  694M 2012-08-21 15:54 #sql_3e03_12.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-08-21 15:46 #sql_3e03_12.MYI
-rw-rw----  1 mysql mysql  756M 2012-08-21 15:54 #sql_3e03_13.MYD

I have no idea how does it created, maybe temp tables ? Is it possible to check the files still used by mysqld or can be remove ?

My version is: Server version: 5.0.45-log Source distribution


texts: index does not get used when there is "OR" in the query

texts:
I have an index on the check_timestamp column, the index gets used by the following query

... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY  check_timestamp  NULLS FIRST LIMIT 10

But the index does not get used with the following query

... where (check_timestamp < NOW() - INTERVAL '30 days' OR check_timestamp IS NULL) ORDER BY check_timestamp  NULLS FIRST LIMIT 10

Why is this happening? any way around it?

Below is the database schema

id  uuid [gen_random_uuid()]
street_db   text
check_timestamp timestamptz NULL
last_accessed   timestamptz NULL
website text
street  text
in_use  boolean [false]
zipcode_db_id   uuid NULL


texts:
I ran into a situation with two options:

Use a single query with a cost of ~0.07

INSERT INTO @vals
SELECT ...
  FROM ...
  JOIN ...
  JOIN ...
 WHERE a.col = 1 OR b.col IS NOT NULL

Use two queries to get the same result -- the first cost ~0.05, the second cost ~0.3

INSERT INTO @temp
SELECT ...
  FROM ...
  JOIN ...

INSERT INTO @vals
SELECT ...
  FROM @temp
  JOIN ...
 WHERE a.col = 1
UNION ALL
SELECT ...
  FROM @temp
  JOIN ...
  JOIN ...
 WHERE b.col IS NOT NULL

I chose the first option - single query, so less concern of mutating data between queries and less overall cost.  Was this the prudent choice?

This was for an operation that originally took ~5+ minutes, timing out in our application.  The re-write got that down to 1.5 minutes consistently, using table variables with unique clustered indexes.


texts: SQL Server - drop user triggered silent revoke of privileges

texts:
I encountered following scenario:

A user X granted a ton of permission in the SQL Server database. For example it granted SELECT permission on table Y to user Z.

A user X was dropped with simple DROP USER statement.

No errors encountered. User X disappeared and all permissions he granted with him - including user Z select permission o table Y.

Question 1. Shouldn't there be an error preventing me from dropping user with this kind of implications?
Question 2. Is it controlled by any database option etc.
Question 3. The DROP USER in documentation has no remarks indicating this kind of behaviour - can any elaborate what I'm missing?

Thx

For reference it was SQL SErver 2016 server but database was running in 2008 compatibility mode.


texts: To find a version for your pc check with the software publisher install mongodb

texts:
I want to install MongoDb on windows 8 but when i try to run C:\mongodb\bin\mongod.exeiget this error:

To find a version for your pc check with the software publisher.


texts: Bad execution plan when having partial index and big In-clause in Postgres

texts:
Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements.

Given the following table:

create table foo(id bigint primary key, bar bigint);

insert into foo (id, bar)
select g.id, case when id % 1000 = 0 then id else null end
from generate_series(1, 10000000) AS g (id) ;

--Create partial index
create unique index ix_foo_bar on foo(bar) where bar is not null;

analyze foo;

And the given the following query with a large in-statement:

explain analyze select count(*) from foo where bar in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101);

The query plan shows a sequential scan. It is slow and and have a high cost:

QUERY PLAN
------------------------------------
 Finalize Aggregate  (cost=612955.35..612955.36 rows=1 width=8) (actual time=254.605..254.605 rows=1 loops=1)
   ->  Gather  (cost=612955.13..612955.34 rows=2 width=8) (actual time=254.474..258.242 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=611955.13..611955.14 rows=1 width=8) (actual time=247.743..247.744 rows=1 loops=3)
               ->  Parallel Seq Scan on foo  (cost=0.00..611955.03 rows=42 width=0) (actual time=247.740..247.740 rows=0 loops=3)
                     Filter: (bar = ANY ('{1,2,3,4,5(...)
                     Rows Removed by Filter: 3333333
 Planning Time: 0.867 ms
 Execution Time: 258.323 ms

set enable_seqscan has no effect - it still does a sequential scan.

If I add a 'not null' to the query, it uses the index:

explain analyze select count(*) from foo where bar is not null and bar in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101);

Index only scan:

    QUERY PLAN
----------------------------------------------------------------------
 Aggregate  (cost=153.55..153.56 rows=1 width=8) (actual time=0.267..0.267 rows=1 loops=1)
   ->  Index Only Scan using ix_foo_bar on foo  (cost=0.29..153.55 rows=1 width=0) (actual time=0.262..0.262 rows=0 loops=1)
         Index Cond: (bar = ANY ('{1,2,3,4,5,6 (...), 101}'::bigint[]))
         Heap Fetches: 0
 Planning Time: 0.531 ms
 Execution Time: 0.319 ms
(6 rows)

It also uses the index if I only have fewer elements in the in-clause (the cut-off is at 100 vs 101), or if I have a complete index instead of a partial one.

Why doesn't Postgres use the partial index when I have an in-clause with more than 100 elements? Is this a known limitation of the query planner, or  is it a bug?


texts:
I would like to use Oracle's automatic memory management, with a limit of around 4GB. (Past experience has shown this to be plenty for my dev PC and that it is easier to get an idea about any performance issues with less than that.)

I believe these are the instructions to follow:

Oracle 12 automatic memory management

It says in short, to enable automatic memory management, set memory_target and (optionally) memory_max_target. The former parameter should be a dynamic value and the latter a harder limit, that can only be changed when stopping/starting the database.

I set these as follows as sysdba:

SQL> alter system set memory_target = 4G scope = spfile;

System altered.

SQL> alter system set memory_max_target = 8G scope = spfile;

System altered.

and check the values with

SQL> show parameter target;

Scope can be just "memory" for the current uptime, "spfile" or "both"; I pick the spfile as I will be restarting:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

That, however, was too simplistic:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 13104M

It is not hard to revert, but in my opinion also not immediately obvious how to get the memory adjustment through, so I will post what I did below and the senior posters can correct/shred me. In either case I think it is nice to have it in one place here.


texts: How to design a versioned database with PostgreSQL?

texts:
I am planning to create a back-end aka an API. The database will be PostgreSQL. I would like to discuss the following scenario:

How can I have multiple versions of the same database? I would like to be able to serve data from an older database structure (v.1) to older clients while serving a newer database structure (v.2) to newer clients. I would like to maintain multiple database versions since I would like to avoid to write workarounds for each version in the surrounding API.

Please leave you architectural thoughts.


texts: how to give access to view only one database to a particular users in sql server 2008

texts:
I need to give 5 different users to access one database, hiding other databases.

I have tired to give access, i am able to give only one user not for all.


texts: Aurora Serverless alter table Error 1114 table is full

texts: Using Aurora Serverless, when attempting to alter table mytable add column mynewcolumn decimal(10,3) after myoldcolumn I receive "ERROR 1114 (HY000): The Table 'mytable' is full". The table has hundreds of millions of rows. Normally this error results from a full partition, but since I'm using Aurora Serverless, I can't manually provision partition space. What should I do to avoid this error?


texts:
After setting a cluster with 3 shards, 3 config server with 2 mongos.

I see these errors in the monitoring agents    Failure during discovery.

What do they mean I tried to see it on the internet but no luck.


texts:
We have been using this syntax when using OpenRowset to collect data from other SQL Server instances

SELECT a.*
FROM OPENROWSET('SQLNCLI11',
'Server=STARDBS1004\T12;UID=user;PWD=pwd;',
'select * from [MyTable]') as a

Native Client is deprecated and MS advice is to use latest Microsoft® OLE DB Driver 18 for SQL Server®

I am struggling to find the correct syntax for my OPENROWSET query

This I how think it is supposed to be now:

SELECT a.*
FROM OPENROWSET('MSOLEDBSQL',
'Data Source=STARDBS1004\T12;Initial Catalog=Staging_DWH;User ID=user;Password=pwd',
'select * from [MyTable]') AS a

But I get these errors:

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
  "Invalid authorization specification".
  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
  "Invalid connection string attribute".
  Msg 7399, Level 16, State 1, Line 2
  The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an
  error. Authentication failed.
  Msg 7303, Level 16, State 1, Line 2
  Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Obviously the syntax is wrong, so can anybody help in finding the correct syntax?


texts: Stored Procedure to evaluate column if condition is true execute another stored procedure

texts:
I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case statement but I get a invalid syntax near CASE and according to this response executing a stored procedure in a select or case clause is not allowed how would I go about executing a stored procedure on a table if a condition is met.

This is the stored procedure:

 SET NOCOUNT ON;

 UPDATE Supply
 SET [Quantity] = [Quantity] - [SupplyHalfLife]

 UPDATE Supply
 SET SupplyAlert = CASE WHEN Quantity <= SupplyQuantityAlert THEN 'Y' ELSE 'N' END

and this is what I want to add to it:

SELECT a.SupplyName
CASE a.SupplyAlert
WHEN 'Y' THEN dbo.sp_SendMail @body = a.SupplyName END
FROM Supply a

which checks column for a alert status of 'Y' and executes a stored procedure.

Code I finally used:

DECLARE @name varchar(255)

DECLARE cur CURSOR LOCAL FOR
SELECT SupplyName FROM Supply WHERE SupplyAlert = 'Y'

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC dbo.SendMail @name

    FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

link