texts: Oracle Database Client Installation 12c minimal install - Centos / Red Hat 7

texts:
I'm looking at reducing the Linux package prerequisites when installing the Oracle Database Client 12c on the below headless application server / stack:

Install file: V839967-01.zip
Application server Linux OS: CentOS Linux release 7.6.1810 (Core)
Application server platform: x86_64
Database Server hosting DB: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database Server Linux OS: Red Hat Enterprise Linux Server release 7.3 (Maipo)
Database Server platform: x86_64

The below Oracle guide, on page 3-9 includes a number of packages that seem unnecessary for example smartmontools.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lacli/database-client-installation-guide-linux.pdf

The installer is being run silently using a response file:

./runInstaller -silent -force -debug waitForCompletion=true -responseFile /u01/app/12c_installer/client/client.rsp

There is also no need for the 32bit client on the application server.

Would trimming the package selection down to the following packages introduce any problems? :

bc.x86_64
binutils.x86_64
compat-libcap1.x86_64
compat-libstdc++-33.x86_64
glibc.x86_64
glibc-devel.x86_64
ksh.x86_64
libaio.x86_64
libaio-devel.x86_64
libgcc.x86_64
libstdc++.x86_64
libstdc++-devel.x86_64
make.x86_64
net-tools.x86_64
sysstat.x86_64


texts: After renaming postgres user, getting "role 'postgres' does not exist" in logs on PostgreSQL startup

texts:
For security reasons, we've been asked to rename our postgres user in our database. We've made the change in our staging environment and bounced PostgreSQL. We're now seeing an error in the log on server start up. We thought it was related to auto-vacuuming but we've since turned that off and we are still seeing the error:

< 2016-01-19 13:42:55.910 EST    >LOG:  database system was shut down at 2016-01-19 13:42:43 EST
< 2016-01-19 13:42:55.917 EST    >LOG:  database system is ready to accept connections
< 2016-01-19 13:42:56.869 EST [unknown] [unknown] [unknown] >LOG:  connection received: host=[local]
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >LOG:  connection authorized: user=postgres database=postgres
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >FATAL:  role "postgres" does not exist

I'm still able to login using my user account and our app is also able to login.

I'd like to be able to be sure nothing else is broken (and eliminate this error message) before pushing this change to our production environment.

FWIW, I see the same error in the log after su - postgres and then running psql. It looks like there may be some process that's assuming that peer logins to the database from localhost are allowed. How would I identify this process and fix it?

This is on Cent OS 7 and PostgreSQL 9.3. PostgreSQL is being started via a service using the default postgresql-9.3.service file.

Here's the output from systemctl status postgresql-9.3:

postgresql-9.3.service - PostgreSQL 9.3 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.3.service; enabled)
   Active: active (running) since Tue 2016-01-19 13:42:56 EST; 5min ago
  Process: 23847 ExecStop=/usr/pgsql-9.3/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 10944 ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s (code=exited, status=0/SUCCESS)
  Process: 23861 ExecStart=/usr/pgsql-9.3/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 23856 ExecStartPre=/usr/pgsql-9.3/bin/postgresql93-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 23866 (postgres)
   CGroup: /system.slice/postgresql-9.3.service
           ├─23866 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data
           ├─23867 postgres: logger process
           ├─23869 postgres: checkpointer process
           ├─23870 postgres: writer process
           ├─23871 postgres: wal writer process
           ├─23872 postgres: archiver process
           └─23873 postgres: stats collector process

Jan 19 13:42:55 snapp-db1.mobdev.local systemd[1]: Starting PostgreSQL 9.3 database server...
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >LOG:  redirecting log output to logging c...rocess
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >HINT:  Future log output will appear in d...gres".
Jan 19 13:42:56 snapp-db1.mobdev.local systemd[1]: Started PostgreSQL 9.3 database server.
Hint: Some lines were ellipsized, use -l to show in full.


texts:
I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 GB file (which is practically empty).

Now, the tricky bit: the server to which I'm migrating only has 3 700 GB volumes, meaning I need to somehow dissect this goliath into three equal chunks. Most advice I've found involves creating 3 target files and running DBCC SHRINKFILE EMPTYFILE on my main file to empty it into the targets, but that'd take ages with a database this large.

Is there a recommended method for splitting a database this large? I'm considering using the Sql Server Integration Services Data Export feature to dump the data into a clone database with the proper file structure, but I'm curious as to whether there's a better way.


texts: One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum of the other BLObs but may be significant less if some of the data has been rejected for various reasons. The raw data was originally kept in a directory structure but there were some issues with logical naming of directories and also with someone changing some directory names. Therefore keeping all the data in the database would appear the correct way to do it. The raw data is needed in case a different algorithm is developed and it needs to be reprocessed. The problem is that as information is added the performance is degrading. If a copy is put on another server and the six raw data BLObs are deleted performance improves significantly. Research on the web suggests that the raw data is read even if it is not used in the query. Creating two tables, one for the raw data and the other for the result of processing, seems to be a solution but this would mean repeating some of the meta data which seems bad design. Is there a better way of tacking this?


texts:
I have a separate database server and a separate reporting server. I would like to upgrade and migrate the database server to a separate server and do not to touch the reporting server. Since the reporting server License comes with the SQL server license.
Can I keep the existing reporting server and use the database server on a different license, or should I migrate both, the database and the SQL server reporting server with the same license?


texts: In Extended Events what is the difference between rpc_completed and sql_batch_completed?

texts: I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch.  However can anyone explain in concrete terms what the difference is?  When should I use one and when should I use the other?  Will most/all SQL commands trigger both events?


texts:
I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.

select count(*)
from table1
where col1 = 123
and col2 = '1';

The estimated cardinality for one value of col1 is roughly 29k, when the actual rows it returns is 637.  For another value, its plan cardinality 460k when the actual rows is 67k.  In general, this is causing it to choose very bad plans..

I've tried updating stats:

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1');

or

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1',estimate_percent =>100);

And I've tried various values of dynamic_samping (1-10), but nothing changes it signifcantly:

select /*+ dynamic_sampling(t1 10) */ count(*)
from table1 t1
where col1 = 123
and col2 = '1';

dyanmic_sampling will alter, but its still off by nearly a factor of 50-100 times.

How can I get better estimates?


texts: Suggestions for SQL query repair tool or tips on building one?

texts:
Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it.

Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 and are requiring us to move to Oracle 11g.  I didn't think this would be a problem until nearly half of the SQL queries started failing.  Upon research I found that previous versions of Oracle had a bug that didn't throw an exception at malformed ANSI SQL queries, and that bug was fixed in 11g.

http://boardreader.com/thread/Ora_00918_After_Upgrade_to_11g_lgv5X1bw1c.html
https://forums.oracle.com/forums/thread.jspa?messageID=4298123

To make a long story short, the brilliant minds that came before me wrote some awful queries and only basic integration tests, and I now have a few weeks to fix about 1000 malformed queries or we are dead in the water.

I will literally set myself on fire if I have to manually fix all of these, and was wondering if you guys knew of some tools or techniques for hunting down these offending queries to make this slightly less painful.  Perhaps a special code profiler, or even a brilliant regex that can help me locate column references without an alias?


texts: How can MongoDB elect a new primary when a datacenter has failed?

texts:
I am curious about the following situation when setting up a MongoDB cluster.

2 Datacenters, 2 nodes in each data center for a total of a 4 node replicaset. One of the two datacenters has become unavailable for any reason, thus blacking out the primary and one of the secondaries.

Per fault tolerance section of https://docs.mongodb.com/manual/core/replica-set-architectures/: I need at minimum 3 votes to elect a new primary, but will only have 2 surviving voters.

I am unable to extend into a 3rd datacenter.

How can I setup the cluster to be able to withstand a datacenter failure and continue to write in the surviving datacenter?


texts: Is Star/Snowflake database design good for applications? What is the alternative?

texts:
I need to solve an exercise for a pharmaceutical company, they ask me to create a database structure from scratch given a few informations.
Because I want it to be scalable and "future proof" I decided to follow the star/snowflake structure. For now it looks more star than snowflake but the idea is there. I have a particular question about the main Fact Table (Study) and how to represent the Dimension Table Status.
I came up with 2 options:

OPTION 1: Fact Table Study > Foreign Key > Dimension Table Contract > Foreign Keys > Dimension Table Status.

So we pass through two Dimension Tables in order to represent the Status on the Fact Table. I will do that through a JOIN in a view or whatever...

OPTION 2: Fact Table Study > Foreign Key > Dimension Table Status.

This way we link directly the Fact Table (Study) to the Dimension Table Status.

Should I go for Option 1 or Option 2?
I'm afraid to link too many Dimension Tables to the Fact Table. This is the first time I try to create a database structure.
Any advice is welcome, any suggestion is welcome, especially from seasoned database architects but also from amateurs.
Thank you

EDIT: adding a few more info

Thank you @AntC for your question. In fact this is not at all a Data Warehouse scenario, the scenario is that of a pharmaceutical company that needs a new software to track their clinical trial.
But of course the most known schemas are Star/Snowflake and I don't want to use any hierarchical schema. At the same time I want to avoid shapes like triangle, diamond, circle because even if now there are only 100 users who knows what this database will be in 10 years. The idea is to shape something for the long term and in my opinion the Star/Snowflake shape is choose also for normal application as far as I know.


texts: ERROR: insufficient columns in PRIMARY KEY constraint definition

texts:
I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration.
Table DDL:
CREATE TABLE c_account_p
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);

When I try the create table, it's throwing below error:
ERROR:  insufficient columns in the PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
SQL state: 0A000

Is it mandatory/necessary that the partition column should be a primary key? cause if I  include load_dttm as PK then its working fine.db<>fiddle
If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY.
Could some please help me to understand this scenario?
Thanks.


texts: Inserting Missing day of data into table - Need to increment primary key by 618 for each key

texts:
So I recently switched servers, but there is a day inbetween the switch of servers where the data didn't get carried over. I have exported the missing data in a CSV.

I know there is 617 rows to be inserted into the "primary" database, problem is there is entries which have already taken the "primary key" (in this case, it is called "id"). This ID auto increments by 1.

What I would like to do, is the following:

starting at id = 552611 ADD $ID + 618 & repeat till end of table.

This would allow me to insert the 617 keys starting at:

id = 552611

Ending at:

id = 553228

My SQL-Foo is not that strong! Can anybody point me in the right direction?


texts: Can't find missing packages Oracle Linux 8

texts:
super newbie here. Trying to learn DBA stuff.
I've been trying to install oracle database 18c on Oracle Linux 8 but there are two missing packages that are just not being found. I need these to complete the installation properly:
 nothing provides compat-libcap1 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64
- nothing provides compat-libstdc++-33 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64

These two commands do not work.
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm
I've looked a lot online.
Thanks in advance.


texts: I have a SQL Server 2008 R2 Standard instance in a "DMZ", so that it can be client-facing. I am trying to have it write its backups to a central location that is within the network. The engineers suggest that they can open a port for me. Hence the question: What port is used by SQL Server to write backups to a remote server?


texts:
I am trying to figure out the best way to sort this solution.

I am trying to produce a SP (or series) to generate invoices so that the contractors know how much they will be paid.

I have created two views that query the staff allocation table and produce the invoice lines

select * from EventInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend
union all
Select * from PTSInvoiceLineView WHERE [date] >= @invoicemonthstart and [date] <=@invoicemonthend

Order By StaffID Asc, [date] Asc

This gives me my invoice line table.
[StaffID], [EventID], [JobType], [EventName], [date]

So I now need to use this information to produce an invoice header for each staff member that has a record from these views.

my invoice header has:
InvoiceRef (PK, Int, not null)
InvoiceDate (date, not null)
StaffID (FK, int, not null)
InvoicePayDate (date, not null)
InvoiceSortCode (nvarchar(6), not null)
InvoiceAccount (nvarchar(8)), not null)

so... I am thinking

Insert Into InvoiceHeader
    InvoiceDate = date.now
    StaffID = cycling through the StaffID from the view
    InvoicePayDate = @scheduledpayday
    InvoiceSortcode = (Select [sortcode] from tblStaff where [staffid] = staffid)
    InvoiceAccount = (Select [Account] from tblStaff where [staffid] = staffid]

I then need to get the InvoiceRef PK int and use that to insert the rows from the view into my InvoiceLineItems which is laid out as:
    LineID (PK, Int, not null)
    InvoiceRef (FK, Int, not null) -- Invoice header record id
    JobID (int, not null) -- JobID from View
    Jobdate (date, not null) -- date from View
    JobType (nvarchar(10), not null) -- JobType from View
    JobName (NVarchar(250), not null) -- EventName from View
    JobPaid (money, not null) -- Calculated field

Any hints, tips, pointers or templates would be greatly appreciated


texts: Can I restore a table with flashback database even if I used "drop table purge"?

texts:
If I issue the following statement:
drop table DontDrop purge;

It's going to be flushed out of my recycle bin so flashback statement won't work:

select * from DontDrop as of timestamp to_timestamp(sysdate-1);

Now, can I use a flashback database statement to restore it?

flashback database to timestamp (sysdate-1);


texts: Database Design - One table from many source(One to One)

texts:
I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, but i don't know which is the best.

Solution 1 :

Text(
text_id PK
content
source_type (tweet, sms, email, etc)
source_id (can't be FK since it may contain sms_id, tweet_id, or email_id)
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Solution 2 :

Text(
text_id PK
content
sms_id FK
tweet_id FK
email_id FK
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Please note that one 'Text' only came from one source.

so if I use solution 2, let say the source is came from sms then the data will look like this

Text(
12
'the text'
333
null
null
)

Sms(
333
'the text'
0818833733664
etc...
)

so which is the best approach ?

UPDATE :
I miss a big part on my question, and that's are may fault, sorry for that. In my case the Text also may came from a csv(excell) file. which mean one file may have many text, because the file is a dump for many text(coma or row separated). SMS, Tweet, and Email relation with Text is one to one, but File with Text is One to Many
so the big missing part in my question is the File Table

File(
file_id PK
file_name
etc..
)


texts: Is it possible to get a "pages touched" statistic from PostgreSQL?

texts: With PostgreSQL one can measure disk reads and cache hits. Is it possible to have a clearer statistic of how many index pages were "touched" to run a query? If so, how?


texts: How to determine master in mysql master-slave

texts:
I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down).

My application server needs to direct all writes to the current master, but I cannot use server level HA between the master and slave (heartbeat, keepalived) since the two db servers are on completely different subnets in different physical locations.

I think this is something that I need to handle at the application level. I can query the two servers and ask which one is a master, then perform all queries to that one.

Is there a query in MySQL to see if the current server is a master in a master-slave replica?


texts: Company supplied data list, with customer additions

texts:
Imagine a company/supplier supplied table of items
table - blueprints
 id [pk]
 name
 color

blueprints
- 1 - chair - pink
- 2 - table - blue

As a company we would supply blueprints as a global list,
the customer would have a local copy of that list.
The customer would need to add more blueprints which would add rows to their local copy (which is a bad idea)
Occasionally as the company/supplier would have additions to the blueprints table.
Ideally after several iterations of updates from the supplier, it would be nice that across all customers the supplied blueprint list has the same id across all customers.
Im sure this is know problem, or there is a better solution for this.
Any thoughts / ideas / existing posts / explanations of what this is, with a proper solution?


texts: I just came across ProxySQL. I have one simple question, if I were to use ProxySQL, do I still need to set up Replication(master-slave) first for MySQL server? Because there is hostgroup(0,1) in ProxySQL which to determine which MySQL server master and slave based on the hostgroups. Or just configure in ProxySQL? Kindly clarify on which steps should I use. Thank you.


texts:
I would like to comprehend the different behavior of like and =. Sadly, I can not reproduce the following simple example thus that both queries return a different result:

SELECT 'ä' LIKE 'ae' COLLATE "de_DE.utf8";
SELECT 'ä' = 'ae' COLLATE "de_DE.utf8";

both return false.

What am I doing wrong?


texts: what happens when I try to insert data into mysql while table is locked?

texts:
Simple question but somehow impossible to find a clear answer:

What happens when I try to insert data into mysql while the table is locked?

1) INSERT query fails

2) INSERT query waits until lock is released

3) something else


texts:
I'm working on a service that allows users to store data from the bank, but banks give me their account statements in different formats, one of them is the following

  68520157286     0106201809510981PAY TRAN SPEI                          -000000000088640000001321436306018973NON1 CLA24 REF 0000000

I would like to know if someone knows the name of this format and how I can treat it to store it in SQL Server, I receive this format in a txt file, which can contain more than a thousand records. , what strikes me about the format are two things:

First, that at first glance we might think that the information is separated by blank spaces as shown

Secondly, there are many zeros among the quantities, which make it difficult for me to know if they are part of the quantity to be treated or if they are part of a character separation format.

So, my question is to know the name of the format and if it is convenient to treat all that information from SQL to store it or if it should be passed through a previous separation process before trying to put it in the database. Thank you very much everyone for your time.


texts: Is there a way to truncate the microseconds of a CSV value when importing data using LOAD DATA INFILE?

texts:
I'm loading data using LOAD DATA INFILE in 5.5 where the value includes microseconds. I must use DATETIME data type. Currently, I'm using regular expressions to remove them from the CSV prior to import. I'm wondering if there's an option in MySQL to truncate them so it only loads YYYY-MM-DD HH:MM:SS?

Thanks


texts: Why is my replication lagging all of a sudden?

texts:
We run a db.m4.10xlarge instance on AWS for our web app(s) and I've recently set up a read replica as a db.m4.2xlarge instance. We've been experimenting with smaller instances for a read replica but they couldn't keep up with the master. The db.m4.2xlarge had no trouble catching up with the master server within an hour or so - ready to go.

Then last night, something odd happened. Write activity on the replica dropped right down and now I can see the replica has drifted 2.5hrs or so from the master (we have constant write activity on one table with timestamps so it's easy to reliably check).

Does anyone know what the cause of this could be? I can't see anything at all in the logs - it's almost as though we've lost a write thread or something like that.. very weird.

Write IOPS last 24hrs:

Replica lag:

If anyone can provide any pointers that'd be helpful. Both the master and replica are MySQL 5.6.34. No changes have been made since spinning up the replica.


texts:
I have a MySQL table which has columns 93 in it. All first 92 columns are INT(11) and the last column is of type DATE.

|number|0|1|2|3........|88|89|90|ddate|
|      | | | | ........|  |  |  |     |
|      | | | | ........|  |  |  |     |
|      | | | | ........|  |  |  |     |

A amount of 900000 entries per day is inserted to the table to the 2nd column("0" column) and at the end of the day the data in the columns must be shifted by one column to the right. So that at the next day. the insertion can be done to the "0" row again. Insertion to the rows is done by a c program. And the shifting is done by script. What should be the best practice to alter the table using the script to shift columns.
I mean I tried alter table In a while loop which took a large time to finish.Any ideas


texts: Setup certification for SQL Server running on Debian

texts:
I have successfully setup an instance of a SQL Server 2017 instance on the latest Debian release, and a domain that points to that server, and I have gotten the respective certificates by using certbot. I also managed to create a TXT record for the acme-challenge, initiated by using the certbot command to issue the certificates. Buut I am failing to encrypt the connections to the server. For example, when I connect from SQL Server Management Studio (SSMS), I always have to tick the following option in order to connect:

And if I try to connect through the sqlcmd utility, I can only connect without the -N option. I am getting the following error on SSMS (and a less verbose one on the sqlcmd, but pretty much the same):

  A connection was successfully established with the server, but then an
  error occurred during the login process. (provider: SSL Provider,
  error: 0 - The certificate chain was issued by an authority that is
  not trusted.) (.Net SqlClient Data Provider)

The connection fails even if I try to login in the server itself with the sqlcmd utility with the -N flag, with the same error message!

I tried configuring the Apache server by adding the path to two of the generated files, namely the fullchain.pem and privkey.pem in the /etc/apache2/apache2.conf file, but the error persisted.

Then I tried to generate a PFX certificate with OpenSSL, by using the command below, as per https://support.microsoft.com/en-us/help/2914662/how-to-use-pfx-formatted-certificates-in-sql-server.

$ openssl pkcs12 -export -out \path\to\the\PFX\certificate.pfx -inkey privkey.pem -in cert.pem -certfile chain.pem

I imported this certificate into the client, in the certlm, under Certificates - Local computer >> Personal >> Certificates, but the result was the same. I tried also to import this certificate into SQL Server, either through SSMS on Windows or using the sqlcmd utility directly on the Linux host, by running the following SQL query:

CREATE CERTIFICATE msSqlServerCert
   FROM FILE = 'C:\pathToTheKeyPairs\certificate.der_2.cer'
   WITH PRIVATE KEY (FILE = 'C:\pathToTheKeyPairs \certificate.der_2.pvk',
   DECRYPTION BY PASSWORD = '<EncryptionPassword>');

I got the following error:

  The certificate, asymmetric key, or private key file is not valid or
  does not exist; or you do not have permissions for it.

Lastly, I tried a totally different Apache configuration, as per https://hostadvice.com/how-to/configure-apache-with-tls-ssl-certificate-on-ubuntu-18

And even though on the client I got exactly the same error message as I was getting before, on the server itself, by running the sqlcmd utility, I got a different error message:

  Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider:
  [error:1416F086: SSL
  routines:tls_process_server_certificate:certificate verify failed:self
  signed certificate].

So I am guessing the problem is with the way I generated the certificates. Any idea how can I make the encryption work?


texts:
I'm using DbUp, starting from the example I've created a C# program. The example suggest me to put all scripts under Scripts directory. Ok, this is done but, when script number will grow up that directory will be such a big mess.

I would organize that directories with version number. For instance

1.0.0 (directory)

CS001 - baseline.sql

1.0.1 (directory)

CS001 - alter x table.sql
CS002 - create y procedure.sql

1.1.0 (directory)

CS001 - create view z.sql

Obviously, code would take the script in an ordered manner. How can I do that? Where I found documentation and other examples?


texts:
I am trying to track price changes on a daily basis. I have 2 tables Stores and Prices.

Table Stores columns:

s_id
company
address
city
state
zip
phone

Table Prices columns:

ref
storeid
price
mgr
datetime

NOTE: storeid in Prices is a reference to s_id in Stores

What I would like to do is run a query to find all prices from yesterday and compare them to today to see if they have changed. Please understand that I am filing in for the database admin for any advice you can offer.


texts:
I Have a huge MySQL table with a long list of charities. Some of the charities have French characters in them which appear incorrectly.

For example I'm getting uppercase 'É' instead of 'é' in the middle of some names.

I'm wondering how I do a simple find change to correct this. I'd tried a few scripts but they don't execute due to errors.

IN SHORT:
In this table find 'É' and replace with 'é'.

Many Thanks.


texts: Is my case statement broken by the use of outer apply?

texts:
I'm struggling with a case statement not working. Simple enough I need to supply a value of 0 where the question marks are. I have several other case statements which handle separate columns, but this is the simplest. I have used case statements with NULLS before and no issue. My best guess is the OUTER APPLY is screwing with things, but I can't find any documentation to back that up.

SELECT TOP 20
  GIVE_BACK1.CLUB_TOT 'B1_TOT',
  GC.CLUB_USER_VARBL3
FROM
  SUMMIT.CLUB_MBRSP GC
    OUTER APPLY
    (
      SELECT
        CASE
          WHEN B1.CLUB_USER_VARBL3 IS NULL
            THEN '?????????????????????????????'
          ELSE
            B1.CLUB_TOT
        END AS 'CLUB_TOT'
      FROM
        SUMMIT.CLUB_MBRSP B1
      WHERE
        GC.ID = B1.ID
        AND
        (
          (
            GC.CLUB_USER_VARBL3 != 'LT'
            AND B1.CLUB_USER_VARBL3 = LEFT(GC.CLUB_USER_VARBL3,2) + CONVERT(varchar(2),(CONVERT(INT,RIGHT(GC.CLUB_USER_VARBL3,3))-1))
          )
        )
    ) GIVE_BACK1

This query yields this result set

NULL         FY88
NULL         FY10
NULL         FY01
NULL         FY99
NULL         FY97
NULL         FY99
NULL         FY97
NULL         FY02
NULL         FY01
17525.00     FY85
NULL         FY84
...

Any help getting the case statement working, and explanation of the issue or a refactoring would be most appreciated.


texts: postgresql - trouble to order a list obtained by string_agg

texts:
I obtained a list of numbers (and text) by a string_agg.

For instance, that :

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

This is the string_agg:

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

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

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

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

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

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

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

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

Maybe do I understand bad...

Thanks again!

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

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

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

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

Any solution? Any advice?

Thanks again!

Cyril


texts: In Job Step > Properties, only see two tabs: Package and Configuration

texts:
I am trying to set a job step that executes a package to use the 32-bit runtime, as it uses the SQL 10 driver against a SQL 2000 database. The job is running on SQL Server 2012. I see all kinds of examples of how to do this, where the job step properties page has 9 tabs (Set Values, Verification, Command Line, General, Configurations, Command Files, Data Sources, Execution Options and Logging). Execution Options has a checkbox for 32-bit mode.

When I look at my job step, logged in to the server as an Admin, running SSMS as administrator, all I see are two tabs: Package and Configuration. I deployed the packages using the Project Deployment method. How do I get those other tabs to show up?


texts: MariaDB User Define Variable throws warning if it is empty

texts:
I have 2 queries: the first query result will be passed to the second query using a user-defined variable. (This implementation is because of a performance issue with the table after applying proper indexing on large scale database)

The queries look like this:

SET @some_var='';
SELECT sids INTO @some_var
  FROM fsettings
 WHERE cid=1
   AND tid=9
   AND tp=1
   AND IsDelete=0;

SELECT name
  FROM `tlivewsts`
 WHERE id IN( @some_var )
 ORDER BY FIELD( id, @some_var );

I get a result within 1-2 sec. (before it was 30 sec.)

However, my issue is what happens if the first query returns no result (empty variable). Then, the second query throws awarning in mysql.

My question is: Is there any possible way to check whether the user-defined variable contains no result, and then directly return "0 results found"? I know this can be done through function/sp in MariaDB; I want to know if that is possible using a simple query?

I have gone through MySQL user-defined variable in WHERE clause for reference, without finding a solution.


texts: How to retrieve 'y' and 'n' comma separated values as 'yes' or 'no' from a single column

texts:
I have a table in a database with these columns

id     name    facility_Type
1      Taluka    y,n,n,y,n

Now I want to retrieve the comma separated values like this

id      name     Ambulance   ward  male_doctors  female_doctors  operation_theator
 1    Taluka      yes         no      no             yes              no


texts: In a MySQL Master/Slave replication, what would happen if I write to the Slave?

texts:
Several Questions

Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?
If not, how do I ensure that the Slave is in-sync with the Master going forward (either by deleting the Slave entry or copying that entry to the Master manually)?
Most importantly, how do I detect that the tables are out-of-sync?


texts:
I'm using a trigger to capture an audit trail. I want to convert the inserted / updated / deleted value to XML and store it in another table.

My trigger look like this:

DELIMITER $$

CREATE
    TRIGGER `MyDB`.`TriggerAudit` AFTER INSERT
    ON `MyDB`.`settings`
    FOR EACH ROW BEGIN
    INSERT INTO insert_audit_trail (user_uid, table_name, inserted_value)
    VALUES ('the Username', 'the table name', 'Select to XML In Here')
    END$$

DELIMITER ;

In MS SQL Server I can use the FOR XML clause, but I don't know to do this in MySQL. Is there any solution for this?


texts: Execute DDL statements from a query in stored procedure

texts:
I would like to do this in a stored procedure:

Query a specific database, dump results in a table
The results contain DDL statements such as alter, create, IF exists...
Execute the results from step 2 as if executing them in a query window via SSMS

Example of step 2 results are:

Alter database.....
exec sp_addmember....
If not exists....

I'm couldn't find a good way to perform step 3, any thoughts?


texts: Why Does Microsoft SQL Server Management Studio Keep Creating Table Aliases?

texts:
When I create or edit queries using design view, SSMS keeps creating aliases (TableName_1) for some tables for no apparent reason. More annoying is it will also do this if I open existing queries in design view. In other words, it changes my SQL code! Even simple queries have this issue, for example:

SELECT
  dbo.tblCalendar.id,
  dbo.tblCalendar.title,
  dbo.luCalendarType.typeName
FROM
  dbo.luCalendarType RIGHT OUTER JOIN
  dbo.tblCalendar ON dbo.luCalendarType.id = dbo.tblCalendar.type

will be become:

SELECT
  dbo.tblCalendar.id,
  dbo.tblCalendar.title,
  luCalendarType_1.typeName
FROM
  dbo.luCalendarType AS luCalendarType_1 RIGHT OUTER JOIN
  dbo.tblCalendar ON luCalendarType_1.id = dbo.tblCalendar.type

Is there any way to stop SSMS altering my SQL code?


texts: Cannot restart mysql (installed natively) but possibly caused by a Homebrew package

texts:
I've previously been running mySql which I had installed with the dmg files without any issues, but I also have Homebrew installed and noticed some errors today which I fixed successfully but unfortunately seem to have caused issues with mySql.

When I run the start command:

sudo /usr/local/mysql/support-files/mysql.server start

I get the error:

"ERROR! The server quit without updating PID file"

From advice here: https://stackoverflow.com/questions/17424863/error-mysql-manager-or-server-pid-file-could-not-be-found-qnap, I've tried adding my pid to the end of my.cnf file in /usr/local/mysql/mycnf:

pid-file = /usr/local/mysql/data/myfile.pid

and restarted, but that didn't work. I've also tried deleting my error logs and restarting but that didn't work either. My current error log has the following. Hoping someone recognizes the problem.

160120 15:18:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2016-01-20 15:18:44 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --    explicit_defaults_for_timestamp server option (see document$
2016-01-20 15:18:44 3122 [Warning] Setting lower_case_table_names=2 because file system for     /usr/local/mysql/data/ is case insensitive
2016-01-20 15:18:44 3122 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied    )
2016-01-20 15:18:44 3122 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to     create it.
2016-01-20 15:18:44 3122 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-20 15:18:44 3122 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-20 15:18:44 3122 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-20 15:18:44 3122 [Note] InnoDB: Memory barrier is not used
2016-01-20 15:18:44 3122 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-20 15:18:44 3122 [Note] InnoDB: Using CPU crc32 instructions
2016-01-20 15:18:44 3122 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-01-20 15:18:44 3122 [Note] InnoDB: Completed initialization of buffer pool
2016-01-20 15:18:44 3122 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2016-01-20 15:18:44 3122 [ERROR] InnoDB: The system tablespace must be writable!
2016-01-20 15:18:44 3122 [ERROR] Plugin 'InnoDB' init function returned error.
2016-01-20 15:18:44 3122 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-01-20 15:18:44 3122 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-01-20 15:18:44 3122 [ERROR] Aborting

2016-01-20 15:18:44 3122 [Note] Binlog end
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'partition'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-01-20 15:18:44 3122 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'


texts:
I wonder if this question will need to be moved to the SharePoint SE site, but I believe this could be a simple a SQL Server security fix.

A little back story:
Back in April we upgraded our SharePoint SQL Server backend (mirrored) from SQL 2005 to SQL 2014.  We did this by way of mirroring from SQL 2005 (primary) to SQL 2014 (mirror).  I don't believe the security settings and logins were extracted from the old server and placed in the new.  I'll have to reach out to my infrastructure department to see if the server has been completely decommissioned.

My SharePoint Admin is trying to create a new site from the SharePoint Central Admin Site (SharePoint 2010), but gets the following error.

  Error "Directory lookup for the file "D:\Data\NewSiteDB.mdf" failed
  with the operating system error 2(The system cannot find the file
  specified.). CREATE DATABASE failed. Some file names listed could not
  be created."

He told me that he used to be able to use his Active Directory account or a SQL Server SysAdmin account to create new sites, now he can't.  I'm pretty sure that his AD account was not an admin of any kind on the SQL Servers regarding the SQL 2005 configuration.  Further, he stated that he used a SQL Server SysAdmin account (in the SQL 2014 configuration) but it still failed.

What can I do if the old server is decomm'd?
If the server isn't decomm'd, and I can access, how do I extract all the logins, permissions, SID's, etc.?
Is it possible to create a new mapping from the SharePoint Central Admin site to a new SQL/Windows login?

Let me know if you need any other info and thank you in advance for your help!


texts: begin tran instead of use db_name inside a SQL Job for executing Stored Procedure

texts:
I was defining a SQL job yesterday for executing stored procedure at a specific time in the morning. It was an ad-hoc and urgent requirement and hence I developed this procedure on the fly and scheduled it to run every morning.

I was supposed to be just calling the procedure in the job step and define output file in the advanced tab. I had to use below command inside the job step:

use db_name
go
exec sp_name
go

Instead of above, I wrote as below by mistake:

begin tran
go
exec sp_name
go

This sql job didn't fail however it didn't do its intended job.

Output file contains below:

  Job 'Job_Name' : Step 1, 'Step_name' : Began Executing 2020-02-03
  06:10:00

Which seems to be correct however I was wondering that above could keep an open transaction and could cause another issues.

I executed sp_whoisactive(Thanks to Mr. Adam Machanic) and don't see any session running from that time as well as there is no blocking on any table as underlying procedure was having tablock table hint while inserting data into the table.

Is there anyway to find what actually happened to this sql job and why did it not do its intended work as well as if there is anyway to find whether it is still running in the background and could be committed or rolled back. I ran sp_who2 also to see if there is any SPID associated to this job, couldn't find anything.

Ran below query to check the status of all jobs and it shows my job succeeded:

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,MAX(DATEADD(S,(run_time/10000)*60*60 /* hours */
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113))),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled'
               ELSE 'Disabled'
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'
          END [Job Outcome]
FROM   sysjobhistory SJH
JOIN   sysjobs SJ
ON     SJH.job_id=sj.job_id
WHERE  step_id=0
AND    DATEADD(S,
  (run_time/10000)*60*60 /* hours */
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
  + (run_time - (run_time/100) * 100)  /* secs */,
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-20,GetDate())
  group by name, CASE WHEN enabled=1 THEN 'Enabled'
               ELSE 'Disabled'
          END
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'
          END

Any help or input is highly appreciated.

  Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1
  (X64)   May 29 2019 20:05:27   Copyright (c) Microsoft Corporation
  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
   (Build 9600: ) (Hypervisor)


texts:
the longblob is the biggest data type of Mysql.  If I want to save something bigger than 4gb (longblob) how can I do?.

I'm talking about Mysql v-5.+


texts:
Consider a table of

CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
external_id int(11),
number smallint(5),
value varchar(255),
UNIQUE INDEX (external_id, number),
PRIMARY KEY(id)
) ENGINE=InnoDB

Updating the value of number as

UPDATE test SET number=number + 1 WHERE external_id='X'

or

UPDATE test SET number='Y' WHERE external_id='X'

Since external_id-number couple is unique, it will not update if the new value of number exists.

How can I use something like INSERT ... ON DUPLICATE KEY to increase the value of number by number + 1 to reach an available value according to the UNIQUE Constraint and complete the UPDATE task?


texts: MySQL function to return breadcrumb-like string from hierarchical structure

texts:
Show create table:

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `a_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Select:

+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  1 | World   |         1 |
|  2 | Europe  |         1 |
|  3 | USA     |         1 |
|  4 | France  |         2 |
|  5 | Germany |         2 |
+----+---------+-----------+

Question:

how do I best define a function which, passed id 4 as an argument, returns:

"World > Europe > France"

This hierarchical "nesting" by "parent_id" could be arbitrarily deep, not just 3 levels deep.


texts: Convert multiple databases into schemas SQL Server

texts:
Due to a high amount of databases being created on my project and the rising cost per databases I had the following question:

Is there a way to convert multiple databases in SQL Server into one database with multiple schemas?

Thank you for helping out.


texts: Command for setting Inline Length has no effect

texts:
I dropped a CLOB(2G) NOT LOGGED column in a table and recreated it with the same size and the LOGGED attribute.
If I look at the syscat.columns I see that the INLINE_LENGTH is 0. The old column had this value set to 312 which is the minimum value accepted.

However if I try to set the inline length of the column with the following command the value doesn't change

ALTER TABLE MYSCHEMA.MYTABLE ALTER MYCOLUMN SET INLINE LENGTH 312

The command complete successfully but apparently there is no change in the inline length.

The following query returns 1 record with a value of 0:

SELECT INLINE_LENGTH FROM syscat.columns WHERE TABNAME='MYTABLE' AND COLNAME='MYCOLUMN'

I would expect a record with a value of 312. Furthermore if I look at the DDL of the table there is no INLINE LENGTH specification.

I also tried to specify the inline length in the ADD COLUMN statement but the result is the same.

How can it be possible ?


texts: What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

texts:
I have a mysql database that has 250 tables.
I would like to run a query to retrieve:
tableName ; FieldName  ; ConstaintName (Primary or foreign key).

I could use information_schema.TABLE_CONSTRAINTS to retrieve the constarint type
but for the inner join I have no idea what should I use.

What is the best approach to achieve this task?

EDIT:
not Constraint name. Should be CONSTRAINT_TYPE instead of ConstaintName. So we should be able to see (Primary or foreign key) only.


texts:
Here is the multi master setup.
Table is not created on middle master.

log-slave-updates is not enabled in my.cnf file.

Master 1

mysql> create database replication;
mysql> use replication;
mysql> CREATE TABLE T1 (ID INT Not Null) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin;

Master 2

mysql> use replication;
mysql> select * from T1;
ERROR 1146 (42S02): Table 'replication.T1' doesn't exist

Master 3

mysql> use replication;
Database changed
mysql> select * from T1;
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)


texts: I want to get first,second and n th occurent of value in sql database?

texts:
I would like to count Place  occurrences of values for attribute in each row:

id  | attribute      |Occurence

1  | ICT              1   |the first time of ICT
2  | ENGINEERING      1   |the first time of ENGINEERING
3  | ICT              2   |the second time of ICT
4  | ENGINEERING      2   |the second time of  ENGINEERING
5  | ENGINEERING      3   |the Third time of  ENGINEERING


texts: pg_hba.conf and postgresql.conf in old installation directory

texts:
I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is in my old 9.3 directory! How should I fix this?

$ port select --list postgresql
Available versions for postgresql:
    none
    postgresql93
    postgresql94 (active)

$ psql --version
psql (PostgreSQL) 9.4.1

$ psql -U postgres -c 'SHOW config_file'
                 config_file
----------------------------------------------
 /Library/PostgreSQL/9.3/data/postgresql.conf
(1 row)

$ ls /Library/PostgreSQL/9.3/data
PG_VERSION      pg_hba.conf     pg_notify       pg_stat_tmp     pg_xlog
base            pg_ident.conf   pg_serial       pg_subtrans     postgresql.conf
global          pg_log          pg_snapshots    pg_tblspc       postmaster.opts
pg_clog         pg_multixact    pg_stat         pg_twophase     postmaster.pid

$ ls /Library/PostgreSQL/9.4/data
ls: /Library/PostgreSQL/9.4/data: No such file or directory

$ ls /Library/PostgreSQL/9.4
ls: /Library/PostgreSQL/9.4: No such file or directory

$ psql -U postgres -c 'SHOW data_directory'
    data_directory
------------------------------
 /Library/PostgreSQL/9.3/data
(1 row)

$ locate pg_hba.conf
/Library/PostgreSQL/9.3/share/postgresql/pg_hba.conf.sample
/opt/local/share/postgresql93/pg_hba.conf.sample
/opt/local/share/postgresql94/pg_hba.conf.sample

$ ls /opt/local/share/postgresql94
conversion_create.sql  postgres.bki           snowball_create.sql
extension              postgres.description   sql_features.txt
information_schema.sql postgres.shdescription system_views.sql
pg_hba.conf.sample     postgresql.conf.sample timezone
pg_ident.conf.sample   psqlrc.sample          timezonesets
pg_service.conf.sample recovery.conf.sample   tsearch_data


texts: What will be the best data model for membership based websites?

texts:
I am building a paid membership based website where user can signup for different kind of membership. Membership will essentially enable or disable certain pieces of functionality in the website. A user can upgrade his membership.

What I am looking for is a datamodel that will capture the

Payment information
Any upgrades done
Next Due dates

etc...

Thanks


texts:
I need to upgrade from PostgreSQL 9.2.24 to 12.
However, once the upgrade was complete, I see that the size of the data directory for the old and new one is different. The old one (/var/lib/pgsql/data) is 91G, and the new one (/var/opt/rh/rh-postgresql12/lib/pgsql/data) is 31G. Aren't both the directories supposed to be in the same size? Furthermore, from the /var/lib/pgsql/upgrade_rh-postgresql12-postgresql.log, the upgrade steps are these :
Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID in new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  template1
  postgres
  zabbix
                                                            ok
Setting minmxid counter in new cluster                      ok
Copying user relation files
  /var/lib/pgsql/data/base/12923/12880
  /var/lib/pgsql/data/base/12923/12882
  /var/lib/pgsql/data/base/1/12880
  /var/lib/pgsql/data/base/1/12882
  /var/lib/pgsql/data/base/16385/12880
  /var/lib/pgsql/data/base/16385/12882
  /var/lib/pgsql/data/base/16385/16386
  /var/lib/pgsql/data/base/16385/16386_fsm
  /var/lib/pgsql/data/base/16385/16386_vm
  /var/lib/pgsql/data/base/16385/16404
  /var/lib/pgsql/data/base/16385/16406
  /var/lib/pgsql/data/base/16385/16407
  /var/lib/pgsql/data/base/16385/16409
  /var/lib/pgsql/data/base/16385/16410
  /var/lib/pgsql/data/base/16385/16410_fsm
  /var/lib/pgsql/data/base/16385/16410_vm
  /var/lib/pgsql/data/base/16385/16418

Why is still showing the old data directory and not the new one?


texts: Find all items where the 2 latest statuses meet condition

texts:
I have a table in a PostgreSQL database like this:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | EXPIRED
2 | 123    | ...     | PENDING
...

I want to query all items where the last two statuses are 'EXPIRED' & 'PENDING' like displayed in the sample above - 'PENDING' latest and 'EXPIRED' immediately before that.


texts: GROUP BY needs ORDER BY NULL to avoid filesort

texts:
I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid the filesort, and it does indeed eliminate that unsavoury looking filesort condition.

I would think that if no ORDER BY clause is present that the dbms would just present an arbitrary order or whatever is most efficient rather than ordering by some mysterious column which requires a filesort.  It seems strange to me that I need to include an extra direction which basically amounts to saying "don't do anything stupid".

My question is why is this even necessary and is adding ORDER BY NULL actually helping performance?


texts: Listener does not connect to the correct instance

texts:
I have an instance with Always ON and two replicas, one of primary and one of secondary. I have created another instance and enabled Always On, each group located in different instances has a different listener with IP and different port. For the first case the listener works well, that is, it connects to the primary replica, and when the failover is done it behaves well. For the case of the second listener, I do not know why to try to connect through this, I am connecting to the group-instance of the first listener, someone has any ideas ??. For the second case, the replicas are fine, synchronized, the problem is that the listener does not connect where it should.

Thanks


texts: SQL Server: grant all 'public' users the permission to create tables

texts:
I have a set of users who have the 'public' role.

According to the default settings, they can read, update, insert data inside existing tables.

How can I change the database settings to that they can as well create and ALTER tables of schema dbo?

If I click on "Alter role" for the database>decurity>Roles, there is a section 'extended properties" but I am not sure how to use this?

Thank you


texts: Change table from InnoDB to ARCHIVE in MySQL

texts:
I try change my table from InnoDB to ARCHIVE with SQL command:

ALTER TABLE data_obook ENGINE = ARCHIVE;

But it always show me only error:

#1005 - Can't create table 'coincity.#sql-1350_8a' (errno: -1)

My structure:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `data_obook` (
  `market_id` int(6) NOT NULL,
  `curr_id` int(4) NOT NULL,
  `book_id` bigint(20) NOT NULL,
  `type` varchar(5) NOT NULL,
  `price` float NOT NULL,
  `amount` double NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`market_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Can somebody help me with solution? Thank you.


texts: MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin

texts:
I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and the second inserts a single record into this scratch table for use in subsequent PHP code:

Trigger 1 (BEFORE/UPDATE on house_report_details):

DELETE FROM tmp_house_report_details

Trigger 2 (AFTER/UPDATE on house_report_details):

CREATE TRIGGER `trg_create_tmp_house_rpt_detail` AFTER UPDATE ON `house_report_details`
   FOR EACH ROW
      INSERT INTO tmp_house_report_details
         (rpt_week_num, client_id, residence_id, curr_step, curr_step_dur,
          prev_wk_step, prev_wk_step_dur, prev_bal, wkly_rent, curr_bal)

      SELECT (NEW.rpt_week_num + 1), NEW.client_id,
             vw_next_wk_house_assignments.residence_id, NEW.curr_step,
             (NEW.curr_step_dur + 1), NEW.curr_step, NEW.curr_step_dur,
             NEW.curr_bal, vw_next_wk_house_assignments.rent_amt,
             (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt)

        FROM vw_next_wk_house_assignments
       WHERE vw_next_wk_house_assignments.client_id = NEW.client_id
      ON DUPLICATE KEY UPDATE
             residence_id = vw_next_wk_house_assignments.residence_id,
             curr_step = NEW.curr_step,
             curr_step_dur = (NEW.curr_step_dur + 1),
             prev_wk_step = NEW.curr_step,
             prev_wk_step_dur = NEW.curr_step_dur,
             prev_bal = NEW.curr_bal,
             wkly_rent = vw_next_wk_house_assignments.rent_amt,
             curr_bal = (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt)

Here are sample rows from the 'master' table - house_report_details - that has the two triggers. The temp/scratch table, tmp_house_report_details, that the triggers delete/insert into has the exact same schema as the master:

SELECT * FROM `house_report_details` LIMIT 3

id rpt_week_id rpt_week_num client_id residence_id curr_step curr_step_dur prev_wk_step prev_wk_step_dur num_mtgs sponsor_contact chores_flg prev_bal wkly_rent other_chgs amt_paid curr_bal last_updated_by last_updated_dt

9   10         10           3         1            3         2             3
        1                9        5               Y          0.00     -125.00
0.00       125.00   0.00     Elvis Presley   Mar-6-2019 03:37pm CST

10  10         10           6         1            3         3             3
        2              9          7               Y          -150.00  -75.00
0.00       225.00   0.00     Elvis Presley   Mar-6-2019 03:22pm CST

11  10         10           9         1            7         1             6
        4              9          6               Y          0.00     -100.00
0.00       100.00   0.00     Elvis Presley   Mar-13-2019 08:26am CST

The triggers work perfectly in my local dev environment and work on my hosted server environment if I manually update information in the house_report_details table using phpMyAdmin.

The BEFORE/UPDATE trigger to delete the scratch table record also works when the table updates are made from my PHP web pages but the AFTER/UPDATE trigger does not work for some reason and causes my entire table update transaction to fail (as failed trigger execution should).

I cannot find anything to help tell me why a trigger will work when manually invoked (via phpMyAdmin, for example) but not when the same DB update is performed via PHP/MySQL code in my web pages.

My hosting provider is BlueHost and I am running MySQL v5.6.41-84.1 and PHP v7.0.33.

I really want to be able to use the power that triggers provide and hope I can get this resolved quickly so I don't have to convert my triggers into PHP/MySQL code in my PHP pages instead.

This is my first question here, so I hope I have provided enough information to troubleshoot this issue. Happy to provide additional details, if needed.

Thank you,
Steve.


texts: Deleted config.ini in MySQL cluster by mistake

texts:
I was trying to delete cache file of MySQL cluster for rolling restart, but by mistake I deleted the directory /var/lib/mysql-cluster from both the nodes.

I have ndb_1_config.bin.1 file, but how can I recover config.ini in text format.
NDB version is ndb-7.4.12


texts:
I found a possiblity to display Rrqms and Wrqms graphs in AWS RDS Enhanced monitoring page (I use Postgres 9.5.19) but I can't find any detailed explenation about these parameters.

By googling I only found these two very vague explenations:

From http://heshen.lofter.com/post/cfa6d_b1b5cb0

rrqms: The number of read requests merged per second that were issued to the device.
wrqms: The number of write requests merged per second that were issued to the device.

From https://www.codetd.com/en/article/7988617

rrqms: the device associated with the read requests per second is the number of the Merge (when the system call requires the read data, VFS requests sent to each FS, FS if found different read requests to read is the same as the Block data, FS will request the merge combined)
wrqm / s: the device associated with the write requests per second is the number of the Merge.

These explenations though are still very blurry for me. Yes one is read and the other is about write per (milli)second but merged into where? Came from where? Issued by whom etc...


texts: Cannot run job NT Authority\System error

texts:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'.

I receive this error when I try to run the job. I have full permissions; I have also tried to run this job under sa account. Cannot get this to work. Have been struggling with this for an hour. IS there any fix? Although there is replication, the job runs on the local servers.


texts: What is InnoDB Buffer Usage and how to clear it?

texts:
In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage"
It is "95%" even if there is no query running.
Why? It affects my performance in my new queries? There is a way to clear it?


texts:
How to insert rows for each of the type column? If there is two type, type 1 and type 2, then I need to insert two rows and also need to change the order and id value for whole table.
current status:
CHOICE Table
id  choice  type    order
1    AA      1       1
2    BB      1       2
3    CC      1       3
4    AAA     2       4
5    BBB     2       5
6    CCC     2       6
7    DDD     2       7

Required updated table:
Now i wan to insert choice "000"  for each type. The updated table will be look like bellow.  How can I achieve this?
updated  CHOICE Table
id  choice  type    order
1    000     1       1
2    AA      1       2
3    BB      1       3
4    CC      1       4
5    000     2       5
6    AAA     2       6
7    BBB     2       7
8    CCC     2       8
9    DDD     2       9

here, id and order column serialized again.
The actual table is too big, so I cannot insert by edit. Please help for this complex query. I have no clue to solve this.


texts: We have 2 VLDB's - 6TB (primary db, not much OLTP traffic) and 4TB (archive db). Data will be moved/archived (delete from primary and insert to archive) daily from 5 large tables. Currently this server is on 2016 Standard Edition and holds 2 VLDB's and 1 small db. We are planning for an HADR strategy, considering standard edition i can only think of log shipping (sometimes log backup ranges 2-5gb) or FCI. If, we upgrades to Enterprise edition we can use AlwaysOn AG's. My question is what is the best strategy for these 2 VLDB's. And considering more features are getting added in future for primary DB, so size will grow and becomes more high OLTP system.


texts: Is it possible to combine these three join queries into one?

texts:
I would like to combine the counts of the stats by different hit_types into one query. Is that possible?

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 1 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 2 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            4 |
+-----+--------------+

I tried to combine the first two but the numbers are all messed up and it eliminated the first result 'aaa'.

MariaDB [db]> select allurls.id, count(s1.id), count(s2.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 inner join stats s2 on allurls.id = s2.allurl_id and s2.hit_type = 1 where s1.hit_date >= '2018-01-15' and s2.hit_date >= '2018-01-15' group by allurls.id;
+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| cnn |           16 |           16 |
+-----+--------------+--------------+

I expected to see

+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| aaa |            1 |            0 |
| cnn |           16 |            1 |
+-----+--------------+--------------+

Ultimately I want to also include count(distinct(s4.source_id)).

Here is a fiddle: https://www.db-fiddle.com/f/tGP5SbC2AdGgeEwWTAgobf/0


texts:
I want to measure the throughput of read operations in Mongo. By throughput I mean  "how many reads the system process in a given amount of time (every second)".

I run mongotop 1 and I get:

                 ns       total    read    write    2018-05-21T08:01:25Z

 sampledb.samplecol        57ms    25ms     32ms

local.replset.minvalid     28ms     0ms     28ms

     local.oplog.rs         9ms     9ms      0ms

admin.system.indexes        0ms     0ms      0ms

admin.system.namespaces     0ms     0ms      0ms

 admin.system.roles         0ms     0ms      0ms

 admin.system.users         0ms     0ms      0ms

admin.system.version        0ms     0ms      0ms

           local.me         0ms     0ms      0ms

  local.startup_log         0ms     0ms      0ms

Is this means that the throughput of read operations is 25ms?


texts: Possible pitfalls of condensing 2 SQL Server instances into 1

texts:
I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active).  We are planning an upgrade to SQL Server 2008 R2, and are investigating the possibility of condensing these 2 instances into 1 for several reasons (ease of use, fewer client DSNs, cheaper licensing).  The two servers are referenced by each other as linked servers (and that link is used a lot), and there are other servers involved which are also linked servers.

I understand I'll need to refactor all the stored procedures and views which reference the linked servers, but what other potential problems should I look out for?  What should I check/do to determine if this is a good idea or not?


texts: Techniques for checking that data loaded correctly in Postgres?

texts:
Apologies for the newbie question! I am working with a largeish dataset (500m rows, 150GB of data) and Postgres 9.1 running on Debian.

I have a Python import script for importing the source data, which is in the form of multiple CSV files. The script does the following:

Create the database (using Django's migrate command)
Delete indexes and foreign key checks, to improve loading time
Import the data using COPY
Re-create the indexes and foreign keys
Run VACUUM ANALYZE

Currently all this is in a Python script, using psycopg2.

This does the job. However, it's very important that this script loads the data in full, without any errors, every time I use it.

So I would like to check what I can do to make this script more robust.

I'm already handling errors, but I was wondering about validating the data once loaded to make sure that it looks how I expect.

I don't know if this is a standard or sensible thing to do, but given that I know how many entries there are in each CSV file (using wc -l) I could run COUNT queries afterwards to check that the number of rows is as expected.

Here is the full script, anyway - suggestions for improvements very welcome.

files = glob.glob('./raw_data/*_formatted.csv')
if len(files) != 45:
    # Check that we can see all the files we expect.
    print 'Not all files present!'
    sys.exit()

for filename in files:
    print filename
    copy_str = "copy my_table(field1, field2) FROM STDIN WITH DELIMITER AS ','"
    file_obj = open(filename)
    cursor.copy_expert(copy_str, file_obj)
    try:
        conn.commit()
    except Exception as err:
        print err
        sys.exit()

try:
    cursor.execute('CREATE INDEX my_table_field1 ON my_table(field1)')
    cursor.execute('CREATE INDEX my_table_field2_varchar ON my_table(field2 varchar_pattern_ops)')
    cursor.execute('CREATE INDEX my_table_field1_field2 ON my_table(field1, field2 varchar_pattern_ops)')
    cursor.execute('VACUUM ANALYZE')
except Exception as err:
    print err


texts: Access not properly translating TOP predicate to ODBC/Oracle SQL

texts:
I have a MS Access query that is based on a linked ODBC table (Oracle).

The DDL for the underlying Oracle table is:

create table road_insp
(
    insp_id integer,
    road_id integer,
    insp_date date,
    length number(10,2)
);

INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (1, 100, to_date('1/1/2017','MM/DD/YY'), 20);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (2, 101, to_date('2/1/2017','MM/DD/YY'), 40);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (3, 101, to_date('3/1/2017','MM/DD/YY'), 60);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (4, 102, to_date('4/1/2018','MM/DD/YY'), 80);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (5, 102, to_date('5/1/2018','MM/DD/YY'), 100);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (6, 102, to_date('5/1/2018','MM/DD/YY'), 120);

select * from road_insp

   INSP_ID    ROAD_ID   INSP_DAT     LENGTH
---------- ----------   -------- ----------
         1        100   17-01-01         20
         2        101   17-02-01         40
         3        101   17-03-01         60
         4        102   18-04-01         80

         5        102   18-05-01        100
         6        102   18-05-01        120

And the local MS Access Query is:

SELECT ri.*
FROM user1_road_insp AS ri
WHERE ri.insp_id = (
                    select
                        top 1 ri2.insp_id
                    from
                        user1_road_insp ri2
                    where
                        ri2.road_id = ri.road_id
                        and year(insp_date) between  [Enter a START year:] and [Enter a END year:]
                    order by
                        ri2.insp_date desc,
                        ri2.length desc,
                        ri2.insp_id
                   );

The Problem:

The performance of the query is quite poor. Unlike the table in the sample DDL, the real table has about 10,000 records. Similar queries execute instantly, but this query takes several minutes to run.

I've checked the SQLOut.txt log, and the problem seems to be that it is executing way too many individual statements:

SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri"
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )

Question:

Unlike my other local MS Access queries, this query uses the TOP predicate. MS Access seems to be attempting to translate TOP to SQL that the ODBC driver and/or Oracle can understand. However, it seems to be doing a very poor job of it.

How can I improve the performance of this query?


texts:
I have a little problem with a two-node mirror and customer requirements:

There is a maintenance window every night from 01:30 to 03:00, within which I have to run rebuild/reorg and checkdb (physical_only). If Murphy happens both jobs run on the same index/table and SQL Server creates a (mini-)dump. Due to the dump, the principal doesn't respond within the set timeout period and the mirror roles change, leading to app problems.

We just have that 90 minute window during the night and customer/PFE wants us to run both jobs in this window. DBCC runs for ~80min and rebuild/reorg for ~30min.

Any suggestions to get around those Murphy-moments or to avoid that dump?

SQL Server 2012 SP3 CU7 used by an older Axapta Installation DB-Size
900GB-1TB (data files on SSDs)

Index Maintenance with Ola Hallengren:

EXECUTE dbo.IndexOptimize @Databases = 'PROD',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@LogToTable = 'Y',
@TimeLimit = 3600

DBCC CHECKDB also from Ola Hallengren with Physical_only option.

I don't know if there is a "real" need to run those rebuild/reorgs every night but the customer told us to do so (also suggested by MSFT PFE).

We're in communication with the customer about expanding the maintenance window, but I want to know if there are other suggestions/tips.

The actual DBCC CHECKDB error message is:

  DBCC CHECKDB (XXXX) WITH all_errormsgs, no_infomsgs, physical_only executed by Username found 2 errors and repaired 0 errors.
  Table error: Object ID 111391516, index ID 2, partition ID 720 57595795734528, alloc unit ID 72057595826864128 (type In-row data), page (7:14130686).
  Test (IS_OFF (BUF_IOERR, p BUF->bstat)) failed.
  Values are 133129 and -4


texts: Preventing concurrent sends of emails from my application

texts:
I have a system that sends emails for me and my clients every day in the morning.  This process is triggered automatically through access to my website, via an iframe.

Often, I receive the same email twice.

The simple process I created on a MySQL database to control this is as follows:

run this query:

SELECT nome, email
FROM clientes
WHERE DATE_FORMAT(lastsend, '%d/%m/%y') <> DATE_FORMAT(now(), '%d/%m/%y')

send emails
UPDATE clientes SET lastsend = Now();

I need a way to lock this down to prevent the same emails from being sent multiple times on the same day.


texts: TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

texts:
I've got an issue with SQL Server.

The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers.

This happens randomly, once or twice a week, at different times.

Is it an issue with networking drivers ? Virtualization infrastructure? What to monitor to find out? There is no other error/warning log anywhere (windows logs, syslogs, ...).

Full ringbuffer report:

<Record id="33" type="RING_BUFFER_CONNECTIVITY" time="614408705">
  <ConnectivityTraceRecord>
    <RecordType>LoginTimers</RecordType>
    <Spid>0</Spid>
    <SniConnectionId>A4478B00-18A1-4147-A65E-2EB505DCA470</SniConnectionId>
    <SniConsumerError>17830</SniConsumerError>
    <SniProvider>7</SniProvider>
    <State>11</State>
    <RemoteHost>xxxx:xxx:xxxx:xx:xxxx:xxx:xxxx:xxxx</RemoteHost>
    <RemotePort>56201</RemotePort>
    <LocalHost>xxxx:xxxx:x:xxxx::xxxx</LocalHost>
    <LocalPort>1433</LocalPort>
    <RecordTime>1/20/2012 10:12:52.32</RecordTime>
    <TdsBuffersInformation>
      <TdsInputBufferError>10054</TdsInputBufferError>
      <TdsOutputBufferError>0</TdsOutputBufferError>
      <TdsInputBufferBytes>0</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <LoginTimers>
      <TotalLoginTimeInMilliseconds>0</TotalLoginTimeInMilliseconds>
      <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>
      <NetworkWritesInMilliseconds>0</NetworkWritesInMilliseconds>
      <NetworkReadsInMilliseconds>0</NetworkReadsInMilliseconds>
      <SslProcessingInMilliseconds>0</SslProcessingInMilliseconds>
      <SspiProcessingInMilliseconds>0</SspiProcessingInMilliseconds>
      <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>
    </LoginTimers>
  </ConnectivityTraceRecord>
  <Stack>
    <frame id="0">0X00000000019EAC4B</frame>
    <frame id="1">0X00000000019E7A36</frame>
    <frame id="2">0X00000000019EC6BE</frame>
    <frame id="3">0X0000000000CA81BC</frame>
    <frame id="4">0X000000000068B888</frame>
    <frame id="5">0X000000000064EF40</frame>
    <frame id="6">0X000000000064ED12</frame>
    <frame id="7">0X000000000064EA17</frame>
    <frame id="8">0X0000000000AB598A</frame>
    <frame id="9">0X0000000000AB5C25</frame>
    <frame id="10">0X0000000000AB5A56</frame>
    <frame id="11">0X0000000000AB61E2</frame>
    <frame id="12">0X0000000074A737D7</frame>
    <frame id="13">0X0000000074A73894</frame>
    <frame id="14">0X0000000076D2652D</frame>
    <frame id="15">0X00000000772CC521</frame>
  </Stack>
</Record>


texts: Same function in SELECT and WHERE clause

texts:
Beginner question:

I have an expensive function f(x, y) on two columns x and y in my database table.

I want to execute a query that gives me the result of the function as a column and puts a constraint on it, something like

SELECT *, f(x, y) AS func FROM table_name WHERE func < 10;

However this doesn't work, so I will have to write something like

SELECT *, f(x, y) AS func FROM table_name WHERE f(x, y) < 10;

Will this run the expensive function twice? What's the best way to do this?


texts:
Please can you explain the meaning of this sentence!

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instance in UTC, regardless of the TIME ZONE offsets stored in the data. For example

  TIMESTAMP '1999-04-15 8:00:00 -8:00'

is same as the

  TIMESTAMP '1999-04-15 11:00:00 -5:00'

Many thanks!


texts: How to link rows in a many-to-many relationship where types have parents, grandparents etc

texts:
In PostgreSQL 10.4, I have a model/table category with a Foreign Key to itself. The hierarchy can extend to any number of levels: parent, grandparent etc.

I have a model/table product. Each product can have multiple categories and each category can have multiple products.

category table:

id | name  | parent_id
---+-------+-----------
1    name1   null
2    name2   1
3    name3   2
4    name4   1
5    name5   4
6    name6   null

cat_prod relation table:

id | name | product_id | category_id
---+------+------------+-------------
1    name1  1            1
2    name1  1            2
3    name2  2            1
4    name3  3            1
5    name4  4            2
6    name5  5            2
7    name6  6            3

product table:

id | name
---+-------
1    name1
2    name2
3    name3
4    name4
5    name5

If I want to get all products that 'are' in category with id 1 (including subcategories) I need to get products from 1 and 2, 3, 4, 5 because they are children, grand_children of 1.

If I link/add a product to a category (ex:5), what is the best approach?

Every time I add a product to a subcategory add it also to the
parent, grandparent etc

Ex- add it also in 4 and 1 - this adds a lot of rows in database for the product category relation, but needs one query to retrieve products.
Add link just to 5 and use recursive

Just one row in database, but many calls to a db at retrieval.
Another approach

I need also to take in consideration that a Product can be in multiple categories.


texts: SQL Server : Restoring database mirroring once its turned off

texts:
I was running security updates on the sql server mirror setup and I encountered split brain scenario as mentioned here

Should I pause Mirroring and stop SQL Server services before running windows updates?

So to fix this issue I turned off database mirroring.

Now I am trying to find a way to restore database mirroring.

Would simply turning on database mirroring solve the issue? , Would the transaction logs automatically update?
Or Should I take a full back and transaction log and restore the database. I would prefer option 1 if that is possible , because in option I have to setup the logins again .
what's the best practice to resolve a split brain issue?

Incase you are already in a situation where db's on principal and mirror are in recovery mode. (This could occur due to many reasons like certificate expired network failures) etc, what's the best way to resolve such a situation, do you need pause mirroring and fix the issue?


texts: SQL Server 2012 - Deadlock happening daily

texts:
We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour.

The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted mode for SQL Server disks, 4 vCPU (that are seen by SQL Server as 2 CPU with 2 cores).
It runs Windows Server 2012.

As we already had the issue, our supplier asked to set MAXDOP to 1 as they experienced problems with parallelism. It didn't solve the situation.

I ran the DBCC TRACEON (1222, -1) to get extended informations about deadlocks and transmit it to the supplier.

Edit: Example deadlock graph

In XML Format: (I was not able to add it to the post)
XML Format

Edit2: Index Maintenance

The following Job runs Ola Hallengren MaintenanceSolution (IndexOptimize) :

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d SAIDBA -Q "EXECUTE maintenance.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 3,@FragmentationLevel2 = 30,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y',@LogToTable = 'Y'" -b

Edit 3: update on the situation

I did some mining on the error log to get an overview of the time line for deadlock occurrences. It happened three to four times more frequently after a software update (120 per day), but there were already 30-40 per day previously.

As the application supplier doubts about our indexing strategy, he wants us to do it.. We will rebuild all the indices..
We will see if it has positive effects...

What else can I do to investigate/troubleshoot/Reduce the number of deadlocks ?

Short answer

Check ERRORLOG for deadlock frequency (is it regular or very new ?)
Ensure Index Maintenance exists and has run successfully.
Setup a trace either with SQL Server Profiler, an Extended Events solution or via Trace Flag 1222.
Analyze the results of the trace:

What queries were involved ?
Which tables/indices are accessed when it happens and how frequently ?

Anything else ?


texts: Load data from a temporal table to another temporal table in Azure

texts:
Currently we have only a Live Database where all our data resides. I have created a separate Azure server and DB for development.
The DEV DB should be some what up to date with the Live DB for developers to work on.

The Live DB has temporal or system-versioned tables. I need a process that would sync the data from the Live temporal tables to the DEV temporal tables. It should load the sysStartTime and sysEndTime that is in the Live temporal tables to dev(should not generate new sysStartTime  and endTime in DEV).

What is the best way of doing this?

I already tried Azure data sync but it does NOT support temporal tables. Please help.


texts:
I have a existing database with several tables, unfortunately there ins't any data load functionality provided for said database and I am trying to rectify this issue.

I have an insert function that processes and inserts the data we need as needed into the relevant tables and sets up the interlinking fkeys that link all the new data together.

the insert statement is set up to return the new primary keys of all the new rows its created a records table to allow rollback. What I want to do is set up a function where if a row in the records table is deleted it will also delete all its referencing rows in the rest of the database (without having to change all the other tables)

example : http://rextester.com/SKJUJ87661

basically when I run delete from t where tid = 1; I want corresponding data in t(upload records) and u(tables where data was created) to be deleted and v(existing reference tables) untouched

and yes, I know I can just run delete from u where uid = (select t.uid from t where t=1); for the relevant tables, but I'm looking for a more elegant solution then writing a massive delete statement (in the actual system t references 18 rows)

update

using triggers i can do this http://rextester.com/MCDGR84399

but how do I make the trigger scroll through all the Fkeys that t is linked to instead of just one

update = 2

my basic question is there a prettier way to do this :

CREATE OR REPLACE FUNCTION delete_cascade() RETURNS trigger AS
$$BEGIN
   delete from u
   where u.uid = OLD.uid;
   delete from c
   where c.cid = OLD.cid;
   delete from b
   where b.bid = OLD.bid;
   delete from a
   where a.aid = OLD.aid;
   return OLD;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER t_cascade
   AFTER DELETE ON t FOR EACH ROW
   EXECUTE PROCEDURE delete_cascade();

example code http://rextester.com/UAZU30980


texts: Configuring PostgreSQL to match server configuration

texts:
We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram is free. PostgresSQL takes only 600Mb while webserver processes take 7Gb. Average CPU load is 25%.

Software is Ubuntu 12.04 and Postgres 9.1. Database size is 15Gb.

As load increased our application response time increased from 230ms to 450ms over last few months. Application takes 40% share while DB takes 60% of response time.

We cached a lot of things, but was wondering if we could get something by tweaking Postgres configuration. After bit of researched we found that default PostgreSQL configuration (https://gist.github.com/darkofabijan/9453c793ceec1ac6274d) is really conservative and that we should definitely tweak our configuration.

After running pgtune we got following recommended values.

maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
effective_cache_size = 44GB
work_mem = 288MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 15GB
max_connections = 200

Once we started running our PostgreSQL server with recommended values we got somehow regular spikes when application response time jumped to 2000ms+ while increase was in database response time. After running it for couple of hours we reverted to original Ubuntu 12.04/PostgreSQL 9.1 configuration.

Obviously we don't have much experience with running DBs. Both concrete recommendations regarding pgtune suggested values and pointers to good resources would be much appreciated.

Edit 1:

            name            |         current_setting          |        source
----------------------------+----------------------------------+----------------------
 application_name           | psql                             | client
 client_encoding            | UTF8                             | client
 DateStyle                  | ISO, MDY                         | configuration file
 default_text_search_config | pg_catalog.english               | configuration file
 external_pid_file          | /var/run/postgresql/9.1-main.pid | configuration file
 lc_messages                | en_US.UTF-8                      | configuration file
 lc_monetary                | en_US.UTF-8                      | configuration file
 lc_numeric                 | en_US.UTF-8                      | configuration file
 lc_time                    | en_US.UTF-8                      | configuration file
 log_line_prefix            | %t                               | configuration file
 log_timezone               | localtime                        | environment variable
 max_connections            | 100                              | configuration file
 max_stack_depth            | 2MB                              | environment variable
 port                       | 5432                             | configuration file
 shared_buffers             | 24MB                             | configuration file
 ssl                        | on                               | configuration file
 TimeZone                   | localtime                        | environment variable
 unix_socket_directory      | /var/run/postgresql              | configuration file


texts:
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be as small as possible before joining (inner joins for this question) in order to make our queries that tiny bit faster.

Example, should this:

SELECT *
FROM   ( SELECT * FROM table1 WHERE col = @val ) t
INNER JOIN table2 ON col = col2

Be better/faster than:

SELECT *
FROM table1
INNER JOIN table2 ON col = col2
WHERE table1.col = @val

My theory is as follows (this might not be the correct implementation, I am trying to remember from a SQL Server 2008 internals book I read (MSFT Press)):

The query processor first gets the left table (table1)
Joins the second table (table2) and forms a cartesian product before filtering out the necessary rows (if applicable)
Then performs the WHERE, ORDER BY, GROUP BY, HAVING clauses with the SEELCT statement last.

So if in statement #1 above, the table is smaller, the SQL engine has less work to do when forming the cartesian products. Then when you reach the where statement, you have a reduced result set from which to filter in memory.

I could be so far off the mark it's unreal. Like I said, it's a theory.

Your thoughts?

Note: I've only just thought of this question and haven't had chance to run any tests my self yet.

Note 2: Tagged as SQL Server as I don't know anything about the implementation of MySql etc. Please feel free to answer/comment anyway


texts: Create temp tables in Amazon RDS PostgreSQL read-replica

texts:
I have dozens of legacy stored procedures which create temporary tables inside for collecting results for a read-only application.

I've created a read-replica of my PostgreSQL in Amazon RDS and tried to perform this procedures, but failed, as it doesn't allow to create even temporary tables in a read-only transaction.

Are any ways how to solve this issue with minimal efforts?


texts: How does disabling a network protocol affect SQL server performance

texts:
The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if:

disabling Named pipes - or - TCP-IP (so that all applications are forced to use a single protocol) would affect SQL server performance in anyway
using a single SQL server login instead of multiple windows login would affect SQL server performance in anyway (there is no real need for separate logins)

What I am saying is that if I consolidate all connections into few possible combinations as possible, do I get some performance benefit?


texts: PostgreSQL memory spike upgrade from 8.2 to 9.1

texts:
We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers.  Everything seems well with the restore of the databases, and the local websites on the dev computers. What isn't working are some reporting ETL queries.

A particular query runs in under 30 seconds on 8.2.  The core query (we've broken it out) is simply a SELECT statement with 15 joins.  For some reason, when this query is run on 9.1, PostgreSQL spikes in memory usage, acquiring all the memory available on the machine, which is 2.8 GB on my machine. Consequently, the query does not finish in any reasonable amount of time (up to 17 mins compared to 30 secs, haven't let it run forever because I need to be able to use my computer).

What we have done so far:

Replicated config settings for 8.2 to 9.1
Ran EXPLAIN on the query on both 8.2 and 9.1: should run faster on
9.1 Explain 8.2 on explain.depesz.com (Raw data: explain_8.2.dat)Explain 9.1 on explain.depesz.com (Raw data: explain_9.1.dat)
Broken the query down by removing joins. When below any 10 joins,
the query will complete
Examined the joining tables for irregularities
Tested the query on different machines running 9.1
Reviewed Postgres config documentation and changelog
Ran VACUUM and ANALYZE on the db
Rebooted the machine
Updated kernel SHMMAX when postgres wouldn't start after reboot
Ran the query on CentOS box and it exhibited the same behavior
Ran the query on 8.4 Successfully

In my opinion, it seems fairly abnormal for PostgreSQL to use so much memory, and is alerting to the problem.  We just can't find it!

Any comments, thoughts, and ideas are appreciated!

Machine Info:

Ubuntu 11.04 64 bit
Intel i5-2400
4 GB ram
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit


texts: Extracting DDL Events for a database on Azure

texts:
I have been trying to extract DDL changes from SQL Server via Extended Events, we needed to know the type of change and the user who made the change. We wanted to avoid the use of triggers where possible and liked the idea of extended events as they are not part of the running transaction when a schema change is made.

Doing this on an on-premise server seems to work pretty well, populating a queue from a notification listening for DDL_DATABASE_LEVEL_EVENTS.

However, I need this solution to work on Azure so my first thought was to do exactly the same but the service broker does not work on Azure so I then looked at using EVENT SESSION sending the information to a ring buffer but this does not appear to be able to get the same events.

Is it possible to get this information on Azure via the ring buffer or do I have to revert to using triggers?


texts: Question on Oracle Public DB link inside Procedures

texts:
Scenario :
A procedure 'XYZ' is created with DB account 'SCOTT'. The procedure uses a public DB link to connect to another database.
We have another user 'EVE' who has access to execute procedures owned by 'SCOTT'.
If procedure XYZ is executed by EVE , how does the public DB link inside the procedure work? Does it use the credentials of SCOTT to connect to Target DB ( the owner) or does it use the credentials of EVE ( the executor)?


texts: Write Access Client App for SQL Server 2005 Backend

texts:
I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very basic) in MS-ACCESS 2010 to upload things to the database. I just want to test it out to make sure that once I implement the entire database, things will work smoothly and without error. I want to figure out how to just make a single form that will be connected to the database, right now the form will just be a simple single text input with a submit button, that will be uploading barcodes(strings not images).

If I need to clarify anything, please just ask.

Thanks in advance for any help