texts: How can I reliably track stored procedure ( and functions ) use over time?

texts:
I have a lot of stored procedures and functions in database, many are not used any more and I want to remove them. How can I find them reliably ?

I have seen answer that suggests looking into plan cache. That's great start but there is note that retention of these plans has many factors involved. What are those factors ? I would not like to delete procedure of function just because plan cache is missing ( for instance: does option recompile delete plan ? ).

I have also considered using profiler ( running it for 2 months does not sound like a good idea ) or perhaps even making some script to change all procedures to write when they are being used ( that would not help with functions though ).

If it is not possible to find them this way, is there another way? Do you know of any other tool, besides profiler ? ( Log analysis would be too slow as I would need to go over 2 months of data ).

I need this data to be reliable as possible.


texts: Question about 'user process' in the context of Oracle

texts:
The following is an except from oracle documentation.

  Overview of User Processes

  When a user runs an application
  program (such as a Pro*C program) or
  an Oracle tool (such as Enterprise
  Manager or SQL*Plus), Oracle creates a
  user process to run the user's
  application.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm

As my understand, an user process is a piece of software that can connect to an oracle server. You (the user) can start a piece of this kind of software, then connect to oracle. If so, why Oracle creates a user process to run the user's application?

Thanks.


texts: Access linked to SQL with SSMA slow performance

texts:
I'm working on migrating a legacy application's DB (in Access 2003 format) to a SQL Server 2008 back-end (This will be upgraded later on).  We are close to hitting the 2GB size limit in Access and performance is starting to degrade, even with up to date indexes and constant compacts.

I have successfully migrated the data into a SQL DB via SQLServer Migration Assistant (SSMA) for Access 2017, after fixing a few issues with tables not having primary keys.  I am able to access the data through the legacy application as normal, but the data access during load is now taking up to 45 seconds for the initial query compared to 1 or 2 before the migration.  Performance seems to improve after the initial load, however.

I have loaded the application and watched on the SQL activity monitor the requests coming in, and the worst offender at 22 seconds is just a simple select:

SELECT "Id"
FROM "dbo"."Table" "t"

The table itself has a little under 11,000 rows, and the query runs almost instantly through management studio, while taking around the same time running through Access and using the linked tables.

The Access DB is on the same machine and same drive as the SQL DB, and is not going out over the network.  Both are currently being run inside of a VM for testing purposes.

Is there any way to speed up my queries using this method?  Activity monitor is not showing any heavy CPU or I/O use during these queries so I do not think those are having a large effect on performance.


texts:
I know that this Problem was addressed before but my SQL knowledge is not that good to figure it out. Maybe you can help me.

select
    ProductionLine,
    Product,
    POId,
    BatchNumber,
    Sum(Prime) as Prime,
    Sum(Offspec) as OffSpec,
    Sum(InQ) as InQ,
    Sum(Prime) + Sum(Offspec) + Sum(InQ) as Total

from @t2

group by
    ProductionLine,
    BatchNumber,
    POId,
    Product;

I now need a Column PercentagePrime where the column Prime is defined by the alias Total


texts: Can I open a table twice using different recordsets and update the second without affecting the first?

texts:
Context: Windows 7, SQL Server 2000, JScript.

I have a recordset opened as follows:

var cmd = "SELECT * FROM Table1 WHERE Field1 = 9 AND Field2 = 0;";
var oRS = new ActiveXObject("ADODB.Recordset");
oRS.Open(cmd, oConn, adOpenDynamic, adLockOptimistic, adCmdText);

I traverse the recordset using

for ( ; !oRS.EOF; oRS.MoveNext() ) {

While I'm traversing, can I open another view into Table1 and update its values without affecting the traversal? The field being updated is not one of those used in the original SELECT. For example:

var oSEC = oConn.Execute("SELECT Field3 FROM Table1 WHERE Field4 = 1;");
if (!oSEC.EOF) {
    oSEC("Field5").Value = 2;
    oSEC.Update();
}


texts: mysql replication - missing table for foreign key on slave

texts:
i have the following situation:
We're running a simple master slave replication with table filters, so the slaves doesn't have all the master tables.

Master has table A with FK to table B
Slave  has table A with FK to table B which doesn't exists (since it's filtered out)

When a new record is inserted to table A on the master, the SQL_THREAD on the slave is failing due to FK error.

  Could not execute Write_rows event on table DB.A; Cannot add or update a child row: a foreign key constraint fails (DB.A, CONSTRAINT FK_name FOREIGN KEY (TypeId) REFERENCES B (TypeId) ON DELETE NO ACTION ON UPDATE NO ACTION), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;

I've tried to set session and global foreign_key_checks=0
and also tried setting @@global.init_slave='SET foreign_key_checks=0'

none of them seems have effect and i still get the above error.

Is there a way to make the slave ignore FK checks?

Thanks.


texts: Can't connect to ODBC database with pyodbc. Which SQL Server driver should I install on the Raspberry Pi?

texts:
I'm trying to connect to a SQL Server database using a python script. I'm using the pyodbc library to connect to it.

cnxn = pyodbc.connect('DRIVER={SQL Driver}; SERVER=192.168.80.30 ;DATABASE=users;UID=data;PWD=xxxx')

When running the script with the above connection configuration I get:

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

It seems that I don't have the SQLDriver installed. Doing some research I haven't found a driver that works. Which ODBC driver should I install on the Raspberry Pi? I'm using Raspbian.


texts: Why distributed AG is preferred to normal AG for cross datacenter failover scenario?

texts: I have two node WSFC and currently I'm working on DR solution with availability group.I already read microsoft doc about distributed AG.So  my question is what are benefits of using distributed AG compared to normal AG? Can we just stretch WSFC across data center and make this happen with normal AG?


texts: Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

texts:
We want to implement the Always Encrypted feature for our application database. There is a table with a varbinary(max) column which is storing binary data of images, pdf documents, etc.

Does the Always Encrypted feature support varbinary(max) column encryption?
If yes, how to do I implement it using SQL Server 2016?

Any support is greatly appreciated.


texts: SQL Server on Ubuntu - An existing connection was forcibly closed by the remote host after reboot

texts:
I have been running SQL Server on Ubuntu for a while and everything has been working fine but I rebooted the server and now all connections to SQL Server fail with the error:

A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
(Microsoft SQL Server, Error: 10054)

I have tried uninstalling and reinstalling SQL Server, setting various mssql-conf values (since removed again) etc. but nothing I do makes any difference.

Articles relating to Windows seem to point to it being SSL-related but SSL was not enabled. I did try to enable it and set a certificate but then even though I made permissions to the certificate and private key 777 and chowned to the mssql user/group it just told me it couldn't read it and then SQL Server failed to start up so I removed again.

Can anyone suggest anything I can do to get it working again please as I am out of ideas?

Thanks
Robin


texts: SQL Cluster instance does not see all drives

texts:
As DBA on SQL 2014 FCI, I need to do a local backup. However, I do not see all drives available on the VM . I only see the drives added as storage disks in the FCI UI.
in order to see the other drives in the backup menu, do I need to add it to storage disks in FCI?
Thank you,


texts: Why is part of transaction committed when stored procedure faults?

texts:
I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time a supplied argument fails validation). If there is no error count it then proceeds to carry out 7 inserts. Following this, it has commit tran;
Recently I added an 8th insert to the list. An implicit type conversion meant that some inserted data would be truncated if inserted. This threw an error to the SSMS screen but I found the first 7 inserts had committed while the 8th obviously didn't complete.
I appreciate that I could include a try ... catch block to handle errors but if an explicit begin tran; doesn't make the whole block of work autonomous down to the commit, then what is the point? What have I missed?
I get that I perhaps could have wrapped my procedure call in a transaction at that level - but can someone please explain what is going on and why the begin tran appears disrespected when included within the procedure body? If calling the procedure begins an implicit transaction, then shouldn't a faulting step in the proc roll back all changes being effected by the proc - even without explicitly including begin tran in the proc body?


texts: I have a VLDB and I offload the DBCC CHECKDB to another server.  Is it possible to update the dbi_dbccLastKnownGood on the VLDB server so it passes any scripts that check that date like sp_Blitz?


texts: Oracle How to find Debug Compiled Stored Procedures/Functions

texts:
How can I get list of stored procedures/Function which are compiled for debug?

I do not see relevant column in following tables.

DBA_PROCEDURES
DBA_OBJECTS


texts: Create non-sysdba user that can grant tablespace to other users

texts:
I am creating an Oracle role with Admin option Grants so that user with this role assigned, will not be a full sysdba but will be able to create other users and grant them permissions.
What I hypothetically need would look like this - Grant grant tablespace  to someRole
And I don't want use this GRANT SYSDBA TO myuser
Thanks


texts: Installation and shell to move physical and logical SQL Server in azure IaaS VM

texts:
Firts of all excuse my english is not my primary language.

We are creating an azure template to deploy an IaaS machine with disks C and D with windows server, and the idea is also deploy an installation of sql server, so initially the engine binaries and files will be deployed in the C and D drive drives.

We have some questions to see if it is possible to do in azure

Is it possible to install through a template not only the OS but also SQL Server?
If the previous question is possible to do after the installation of SQL Server, is there a script or a guide to create a script to move the logical and physical sql server files without having to enter sql server management studio and execute a t-sql script or move the files manually?. The idea behind this is to automate as much as possible the deploy of the Virtual Machine and to move the files to his respective volumes to fullfill SQL Server best practices regarding the separation of the log data from the user data and tempdb and also the restart of the instance once the previous steps are done.
or is there a way to do the install of the OS and SQL Server and in the template specify wich files of the database have to go to an specific disk and avoid the movement in a further step of the deployment of the machine

thanks in advance


texts: Get records with max value on grouped SQL results (mysql)

texts:
How do you get the rows that contain the max value on created_dt for each by conversation_id grouped set?

I've seen some complicated variations on similiar questions, and none with a good answer.
I've put together a simple example:

id | member_a_id | member_b_id | conversation_id | created_dt          | content
1  | 100         | 110         | 1               | 2018-04-01 12:00:00 | aaa
2  | 100         | 110         | 1               | 2018-04-01 13:00:00 | bbb
3  | 105         | 100         | 2               | 2018-04-01 14:00:00 | ccc
4  | 110         | 100         | 1               | 2018-04-01 15:00:00 | ddd
5  | 100         | 110         | 1               | 2018-04-01 16:00:00 | eee

Desired result set:

member_a_id | member_b_id | conversation_id | created_dt          | content
100         | 110         | 1               | 2018-04-01 16:00:00 | eee
105         | 100         | 2               | 2018-04-01 14:00:00 | ccc


texts:
Server version:

mysql See 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

For the research that does just change the file /etc/mysql/my.conf and insert the new value, that's what I did.

Then according to a mysql documentation it is said:

  There are several ways to see the names and values of system variables: To see the values and a server based on your compiled defaults and all the option files it reads, use this command: mysqld --verbose - - Help

That returns me:

...
ft-min-word-len 2
innodb-ft-min-token-size 2
...

Between when I'm doing a direct check in mysql I have the following return:

MySQL [(none)]> SHOW VARIABLES;

...
ft_min_word_len | 4
...
innodb_ft_min_token_size | 3

At first I researched I should change the file /etc/mysql/my.cnf, where it says:

# The MariaDB configuration file
#
# MariaDB / MySQL tools read configuration files in the following $
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set options only for MariaDB.
# 4. "~ / .my.cnf" to set user-specific options.
Searching for the file containing as a command:

me @ me: / $ sudo find / -name "*.cnf" -print

/etc/mysql/conf.d/mysql.cnf
/etc/mysql/conf.d/mysqldump.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
/etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
/etc/mysql/mariadb.conf.d/50-client.cnf
/etc/mysql/debian.cnf
/etc/mysql/my.cnf
/etc/mysql/mariadb.cnf
/etc/alternatives/my.cnf
/etc/ssl/openssl.cnf
/usr/share/ssl-cert/ssleay.cnf
/usr/lib/ssl/openssl.cnf
/var/lib/dpkg/alternatives/my.cnf

So I changed the archives:

/etc/mysql/mariadb.cnf

...
[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2
...

/etc/mysql/conf.d/mysql.cnf

...
[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2
...

/etc/mysql/mariadb.conf.d/50-server.cnf

...
[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2
...

When, i cheking in mysql it returns the default values 4 and 3 instead of 2 for both.

Is there another way to set these values?

sorry my bad english


texts:
I'm currently trying to compress a table in Oracle with the following statements:

ALTER TABLE MYTABLE COMPRESS FOR OLTP;
ALTER TABLE MOVE;

My question is: the MOVE operation is taking forever and unfortunately I forgot to state the ONLINE clause.

If I cancel it, will I lose data? Or is there any way to find out how long it will take?


texts: SUSDB appears to be in a different location than shown in SQL Studio

texts:
I Have WSUS installed on a 2012R2 server.  I am using SQL, not the internal windows database.

I was looking at cleaning out some old files.  One of the folders is named RestoreTemp (created to restore data to check).  The SUSDB.mdf and SUSDB.log files there have more recent date modified values than the files located where I would expect them to be (Program Files/Microsoft SQL Server/MSSQL12.WSUS/MSSQL/DATA).  When I check the file location in SQL Server Mgmt Studio, it shows the database as being located in the correct location.  However, the date modified is almost 2 weeks before today (the one in the incorrect location shows today's date and very close to the current time).  There are no other files in the incorrect location.

I didn't do any type of database restore for quite some time (I do at least quarterly to verify that backups are working), definitely not within the past couple of weeks.

Anyone have any idea of what is going on?  WSUS is set to synchronize daily, and when I look at the Synchronizations there were some on a date after the date shown on the database in the correct location so I am pretty sure this is not the database being used.  I would like to have the database in the correct location so someone doesn't delete the one in the incorrect location but am afraid I will blow up WSUS.

Thank you in advance for any information you can provide.


texts:
We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit)

Seeing ORA-04020 errors in the logs.

Is going to 11.2.0.2 the only way to resolve the deadlock issue?

Thanks for your assistance.


texts: DB2 doesn't start after change db2 hostname

texts:
After changing the hostname of my db2 server, the db2 services won't start anymore.

After reading the notes on how to rename the db2 hostname I understand what went wrong, some things should have been done before restarting the server.

What are my options to fix in this scenario?


texts: What standard SQL isolation levels do the SQLite isolation levels correspond to?

texts:
There are four standard SQL isolation levels:  Read Uncommitted,  Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)

In SQLite, there are three isolation levels: DEFERRED, IMMEDIATE, or EXCLUSIVE. They control how the required read/write locks are acquired. See https://www.oreilly.com/library/view/using-sqlite/9781449394592/ch04s07.html

  By default, all transactions (including autocommit transactions) use the DEFERRED mode.
  Under this mode, none of the database locks are acquired until they are required. This
  is the most “neighborly” mode and allows other clients to continue accessing and using
  the database until the transaction has no other choice but to lock them out. This allows
  Transaction Control Language | 53other clients to continue using the database, but if the locks are not available when the
  transaction requires them, the transaction will fail and may need to be rolled back and
  restarted.

  IMMEDIATE attempts to acquire a reserved lock immediately. If it succeeds, it
  guarantees the write locks will be available to the transaction when they are needed,
  but still allows other clients to continue to access the database for read-only operations.

  The EXCLUSIVE mode attempts to lock out all other clients, including read-only clients.
  Although the IMMEDIATE and EXCLUSIVE modes are more restrictive to other clients, the
  advantage is that they will fail immediately if the required locks are not available, rather
  than after you’ve issued your DDL or DML commands.

I was wondering if it is possible to make some correspondence between the four standard SQL isolation levels and the three SQLite isolation levels?

Does DEFERRED mean exactly serializability isolation level, but only applies to transactions which consist of only one statement not more than one?
Does IMMEDIATE mean exactly read uncommitted isolation level, which prevents dirty write but not dirty read?
Does EXCLUSIVE mean exactly serializability isolation level?

Thanks.


texts: cant access both phone numbers in my nested table type

texts:
Im using Oracle Developer and I have a customers table where each customer has 1 home-phone number beginning with 0131 and either 1 or 2 mobile numbers. I am trying to display the customers name and phone number which contains 0770 and has more than 1 mobile phone number

I have a nested table in my database which contains the 2 sets of phone numbers.

CREATE TYPE PHONENUMBER_TYPE AS OBJECT(
    int_code VARCHAR2(4),
    area     VARCHAR2(5),
    local    VARCHAR2(8));

CREATE TYPE PHONENUMBER_TABLE_TYPE AS TABLE OF PHONENUMBER_TYPE;

Here is my customers table and type

CREATE type customer_Type AS object(
    cust_ID NUMBER(8),
    address address_type,
    name name_type,
    homephone PHONENUMBER_TYPE,
    cust_mobiles PHONENUMBER_TABLE_TYPE,
    ninum VARCHAR2(8));

CREATE TABLE customer OF customer_Type(
     cust_ID PRIMARY KEY)
NESTED TABLE cust_mobiles STORE AS customer_mobiles;

And I'm entering data into the table using the following insert statement

  INSERT INTO customer VALUES(
      '1002',
      address_type('Adam', 'Edinburgh', 'EH1 6EA'),
      name_type('Mr', 'Jack', 'Smith'),
      phonenumber_type(NULL, '0131', '4960403'),
      phonenumber_table_type(PHONENUMBER_TYPE('44', '07889', '900003'),
      PHONENUMBER_TYPE('44', '07705', '623443')),
      'NI810');

And obviously I am entering both phone numbers into phonenumber_table_type but I cant seem to access both when I'm using the where clause in my query.

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

Which produces the following output:

+-------+-------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |
+-------+-------+----------+-------+--------+

But the expected out put should be

+-------+-------+----------+-------+--------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |    44    | 07889 | 900003 |
+-------+-------+----------+-------+--------+----------+-------+--------+

So how would I produce the expected result?

I though just adding in the another e.* again but that would produce the same number twice.

SELECT t.name.firstname   AS fname,
       e.*, e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

I suspect its got something to do with the where clause. Because when I run the following I get all the numbers in the phonenumber_table_type.

SELECT C.name.firstname, U.* FROM customer c, TABLE(c.CUST_MOBILES) u


texts:
Queries are running very slowly on a table with 80k rows.

SELECT count(*) FROM task takes <100ms. SELECT type, count(*) FROM task GROUP BY type takes 30,000ms.

Basically, any query that is not index-only takes excruciatingly long.

I kill the connections and vacuum repeatedly.

rivet=> VACUUM VERBOSE task;
INFO:  vacuuming "public.task"
INFO:  scanned index "task_id_idx" to remove 1830 row versions
DETAIL:  CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.36 s
INFO:  scanned index "task_type_queue_idx" to remove 1830 row versions
DETAIL:  CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.25 s
INFO:  "task": removed 1830 row versions in 41 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "task_id_idx" now contains 87135 row versions in 23995 pages
DETAIL:  1830 index row versions were removed.
23488 index pages have been deleted, 23428 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "task_type_queue_idx" now contains 8218 row versions in 28362 pages
DETAIL:  915 index row versions were removed.
28219 index pages have been deleted, 28140 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "task": found 666 removable, 3874 nonremovable row versions in 98 out of 1005331 pages
DETAIL:  3174 dead row versions cannot be removed yet, oldest xmin: 70916502
There were 16134 unused item pointers.
Skipped 0 pages due to buffer pins, 1003551 frozen pages.
0 pages are entirely empty.
CPU: user: 0.15 s, system: 0.00 s, elapsed: 0.83 s.
INFO:  vacuuming "pg_toast.pg_toast_133792"
INFO:  index "pg_toast_133792_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_133792": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 70916502
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

But with no success.

Then I vacuum "full."

rivet=> VACUUM (VERBOSE, FULL) task;
INFO:  vacuuming "public.task"
INFO:  "task": found 931 removable, 83522 nonremovable row versions in 1005331 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 3.30 s, system: 0.28 s, elapsed: 29.96 s.
VACUUM

Now my table scan query over 80k rows takes <200ms.

Why was VACUUM FULL necessary?

I notice that before full vacuum there were 1M pages, and now there are <400.


texts:
Should be a simple questions, I just want to make sure.

If I had 2 tables I want to bridge, for example food (food_id, foodname, ...) and a plate (plate_id, platetype, ..). When the tables are bridged, would they need to be foreign keys to each other or it wont be necessary as there is a food_plate table with food_id and plate_id ?

Thanks for the help


texts: Non-Super user permissions required to allow a user to create a schema within a database that is owned newly created user

texts:
I'm able to run the following as postgres or any other user which has SUPERUSER ("dba1" has SUPERUSER in this case)

psql -U dba1 dev << SQL
create user udev1;
create schema udev1 authorization udev1;
SQL

what is the minimal set of discrete privileges required for dba1 which would enable dba1 to create a new user "udev2" and a schema owned by that user "udev2" in the dev database?


texts: How can I export a subset of tabledata from a prodution database into my local testing database?

texts:
We have a relatively big production postgres based db: ~20GB.
The PostgreSQL database is hosted on heroku.

I would like to copy a small subset of the table data to my local database so I can run some tests on them without having to work on production.

I do not want to generate sample data myself, but rather use the data which already exists in the production environment.

~100 rows from each table in the database would be sufficient.
Is there an easy way to accomplish this?


texts: Creating mongodb database with ansible playbook

texts:
How i can create MongoDB database with Ansible? is it possible?

If not, can i do this with bash script?

I tried to use mongodb_user module for Ansible, but it cant work without existing table.


texts: Is deriving a value using multiple joins a good idea?

texts:
I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we should store the final cost.

I am having a few issues with this part of the specification, and the lecturer is not forthcoming with a definitive answer.

  Should this be derived from the total products in the order, or should it be entered in by the application logic when the order is complete?

Can anyone shed some light on how to go about this approach. Because currently I have made the assumption that because it says "database driven application" the whole logic needs to be provided by the DBMS. And I have created Views to achieve the joining of tables, because triggers seemed harder to ensure the integrity of the data.


texts: Mysql Server version: 5.0.77-log Source distribution

texts:
I have a master(M)-slave(S) replication on MySQL. During a failover test, the master was brought down and the slave was made the master by doing STOP SLAVE; RESET MASTER;. Since there are only 2 servers (master and slave) I didn't have any other slave to change master to. On the master server the MySQL data files (ibdata files and bin logs) were deleted by mistake. Can someone please tell me how to bring the master up to speed and change it back to being the master server?
Thanks.


texts: What is the format of EXTERNAL NAME argument?

texts:
When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE?
I tried
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck/cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck.cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:cl.mth'

but either way, I get The user defined function or procedure "mth" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=3.68.61
On Java side, the method is declared as follows:
public void send_mail( // with or w/o static, with or w/o synchronized
    final String P_TO,
    final String P_CC,
    final String P_BCC,
    final String P_FROM,
    final String P_SUBJECT,
    final String P_TEXT_MSG,
    final String P_HTML_MSG,
    final String P_ATTACH_NAME,
    final String P_ATTACH_MIME,
    final byte[] P_ATTACH_BLOB,
    final String P_SMTP_HOST,
    final int P_SMTP_PORT,
    final String P_USER_NAME,
    final String P_PASSWORD

On the DB2 side, the procedure is being attempted to create as follows:
CREATE PROCEDURE send_mail(
  IN P_TO          VARCHAR(4096),
  IN P_CC          VARCHAR(4096) DEFAULT NULL,
  IN P_BCC         VARCHAR(4096) DEFAULT NULL,
  IN P_FROM        VARCHAR(4096),
  IN P_SUBJECT     VARCHAR(32672),
  IN P_TEXT_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_HTML_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_ATTACH_NAME VARCHAR(255) DEFAULT NULL,
  IN P_ATTACH_MIME VARCHAR(100) DEFAULT NULL,
  IN P_ATTACH_BLOB BLOB DEFAULT NULL,
  IN P_SMTP_HOST   VARCHAR(255),
  IN P_SMTP_PORT   INTEGER DEFAULT 25,
  IN P_USER_NAME   VARCHAR(255) DEFAULT NULL,
  IN P_PASSWORD    VARCHAR(32672) DEFAULT NULL
)
LANGUAGE java
PARAMETER STYLE java
FENCED THREADSAFE
EXTERNAL NAME 'javadb2:db2.smtp.send_mail' -- with or w/o db2

I've checked and double-checked that every time I sqlj.db2_install_jar it appears under the function\username folder in the DB2 folder hierarchy.


texts:
A few days ago, in one of my servers, I had the following alert pop out:

  A read operation on a large object failed while sending data to the
  client. A common cause for this is if the application is running in
  READ UNCOMMITTED isolation level. This connection will be terminated.

However, it has only happened once, and I could not deal with it there and then.

where/how could I search for more information as to what was the primary cause(s) that triggered this alert/message?

Edit

Actually this question could be something down these lines:

what should I query at the time the alert was fired, to add more information to the email the DBAs get?


texts:
What does the alias after DELETE mean?
E.g. I have a query:
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p2.Id < p1.Id

What does it mean DELETE p1?
I understand it as follows. First we find a cartesian product of the Person table with itself. Then we remove all the rows where the Email from the both tables (p1 and p2) is not the same. Then we remove all the rows where the Id from the p2 is greater or equal than the Id from p1. Now we are left with some rows. We pick all the Ids from the p1 part of the left rows and remove all the entries from the initial Person table, which has the Id contained in the picked Ids.
Is my understanding correct?
If it is correct, then DELETE p1 means delete all entries in Person where Id is in the resulting p1 part of the rows.


texts: How to optimize performance of backup process? Servers go down during backup

texts:
The context - So I am an intern (So I'll appreciate a more noob friendly response) and we have several websites on a server of which we do a backup daily, at midnight. We use MongoDB and there's a cron job with a script which does a mongodump to a temp dir on the server, the contents of which are then rsynced onto a remote backup server and the temp dir is deleted. We have several websites on there. The database of each is around 2GB, with the exception of two websites with a DB for 4GB and 5GB respectively. In total, all databases are about 23-24GB. There is also a completely separate server with only one website with about 100GB database.

The problem - We monitor our websites using Pingdom and New Relic and each night, during the backups, we get alerts for both servers. The logs don't say much, so I'm guessing they didn't go down completely, just became very very slow. Haven't observed it myself on any of these websites, but I have on a stage website when taking a manual backup. It did come to a crawl.

I am told this didn't used to happen before, even though the way we're backing up hasn't changed, since I came here. I'm guessing, that's because DBs are now larger or there's more of them.

The question - Is there a way to make the whole backup process more performance efficient, so the servers don't go down or come to a crawl?

My ideas - I've been reading about compact and was wondering if you think this would help? I'll probably try it on the stage servers first. Also, is there any way to not write the dump to a temp dir first, but instead to put it directly on the remote server? I'm hoping this would reduce the I/O operations on the live server and improve performance, though I can't seem to find a way to do it.

Some advice will be greatly appreciated, as the project manager is crying about the servers going down at night.

Thanks.


texts:
Here's the case - I have the following query:

SELECT DISTINCT post_id FROM
(
    (
        SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director' AND
            pm.meta_key = 'htk_premiere_date'
        )
    UNION
    (
        SELECT DISTINCT rp.post_id, '' AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director'
    )
    ORDER BY (premiere_date IS NULL), premiere_date DESC
) AS element_ids

The above query works as intended but, as those two inner SELECTs are fairly similar, I was wondering if there is a way to merge them into a single query?

Here's the background:
This is related to a WordPress database, expanded with custom tables. I need to get IDs of elements, sorted by htk_premiere_date meta key value. This key & its value is stored in wp_postmeta - table that has the following columns: meta_id (not important here), post_id, meta_key & meta_value. The point is, I need to sort all IDs in the following pattern:

First, IDs that have this htk_premiere_date key set and it's assigned a date value (so sorted by date).
Second, IDs that does not have the htk_premiere_date meta value set, but the key has its row in the wp_postmeta table.
Third - and this is basically the source of the whole problem - if a post ID does not have a row in wp_postmeta table for htk_premiere_date key, it should be treated as in point 2. - so as if it has the key but no value assigned to it.

Thanks a lot for any help!


texts:
I have installed Oracle 12c R2 on my local machine and have created the HR schema using this link : Installing sample schemas

I also gave GRANTS to HR schema to view data dictionary.

GRANT SELECT ANY DICTIONARY TO HR;
GRANT SELECT_CATALOG_ROLE TO HR;

Now, when I connect  as SYSDBA, I am able to view PDBS.

SQL> select con_id, dbid, name
     from v$pdbs;

    CON_ID|      DBID|NAME
----------|----------|------------------------
         2|3752719039|PDB$SEED
         3|3679838824|ORCLPDB

However, when logged in as HR, it does not show anything.

SQL> select con_id, dbid, name
    from v$pdbs;

no rows selected

I surely have connected to the same instance.The output of this query is same for both connections.

SQL> select SYS_CONTEXT('USERENV','INSTANCE_NAME') instance  FROM DUAL;

INSTANCE
-----------------
orcl2

What am I missing here?


texts:
I understand that in order to fulfill 2.NF, attributes must not be dependent on part of the key. Now, the question is, let's say we have a relation R with a set of attributes {A,B,C,D,E,F,G,H,I,J,K} and its functional dependencies {A→GH, B→IJ, C→A, F→B, FC→DK, K→E} and the candidate keys C AND F. Do these FDs like C→A violate 2.NF because it is not fully functionally dependent on BOTH F AND C ?

So the question is

  Do attributes in functional dependencies need to be dependent on the whole set of candidate keys - in this case FC ? Or is the dependency on one of the keys sufficient to fulfill 2.NF ?


texts: Count null and not null values in a column


texts: What may be the consequences of deleting the system keyspace?

texts:
I have a Cassandra cluster composed of 2 nodes. One node could not start Cassandra yesterday. I deleted the system keyspace directory and I started the node again. The node is running after system keyspace directory deletion.

What are consequences of system keyspace missing?

All data are present in my cluster and I find no errors at the moment.


texts: Run SQL Server 2016 side-by-side with older versions?

texts: Brent Ozar had a post SQL Server 2016 and the Internet: Forced Updates, Phoning Home.  "Issue #1" is ‘You probably shouldn’t run 2016 side-by-side with older versions.’  So, I checked the language in all the SQL Server install ULAs and they all say the same thing.  I know some shared components will be upgraded to the newest version on a server upon install of said version and feature, but this has been around for many Glenn Berry guided instance installs.  Is there something I’m missing?


texts: Does InnoDB ever release unused disk space?

texts:
A large MySQL association table with 500M rows recently ballooned into 1B rows due to a bug (now fixed). Disk usage went from ~130GB to 240GB.

Unfortunately there is not room on this server for any of the standard remedies (OPTIMIZE or ALTER TABLE table ENGINE=InnoDB, dump & rebuild, build into temporary table & rename & drop old table, etc.). I only have 50GB remaining.

Here's the question:

While I've successfully removed over 100M rows so far (1B down to 850M), disk usage has actually grown by 10GB (to 250GB). Yes, new records are also being added every second, but I'm deleting far faster than they are being added.

Will InnoDB ever release this space?
Why does the table disk usage
grow, while I'm shrinking the row count?

Any other advice to reclaim unused disk space when the regular solutions don't fit?


texts: Blocked Process Report SQL Server 31 hour duration

texts:
I have an application (.NET EF 6 ) which is getting timeouts when submitting/reading data from the database. Whilst trying to diagnose the problem I used Blocked Process Reports from SQL server profile to see if I was getting any long blocks. I ran the trace and came across some peculiar very long blocks - 31 hour blocks. I want to know what these are and if I should be worried about them. I filtered the trace by Database name that I was concerned with.

example 1:

    <blocked-process-report>
 <blocked-process>
  <process id="process703b88" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="115168485" ownerId="64139099452" transactionname="PurgeAllEmptyExtents2" lasttranstarted="2015-06-13T04:52:31.830" XDES="0x6b1731270" lockMode="X" schedulerid="6" kpid="4996" status="background" spid="10" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack/>
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="444" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-06-14T12:51:46.637" lastbatchcompleted="2015-06-14T12:51:46.637" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="63627468968" currentdb="33" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack/>
   <inputbuf>
FETCH API_CURSOR0000000000043F19   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

Example 2:

<blocked-process-report>
 <blocked-process>
  <process id="process34a20f288" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="107460436" ownerId="64211456029" transactionname="implicit_transaction" lasttranstarted="2015-06-13T07:00:07.107" XDES="0x1dc183340" lockMode="IX" schedulerid="4" kpid="6712" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T07:01:00.090" lastbatchcompleted="2015-06-13T07:00:07.110" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64211456029" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x01002100f0e6ac0d90d6a500010000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 07:01:00&apos;;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 33:485576768:0 " waittime="114659439" spid="78" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T05:01:00.883" lastbatchcompleted="2015-06-13T05:00:59.767" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64144056143" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x0100210009260608307d4565030000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

The first one seems to be FETCH API_CURSOR0000000000043F19 and the second one is some sort of log purge: exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;

What are these and should I be worired?

Thanks


texts:
I have a table where there are in some cases twin records.

In the table, I have the following columns:

id (identity column)
action_id
session_id
row_id
timestamp
...and more records.

I need to find a twin record for a specific id.

A twin record for action_id=11 is when action_id=12 and session_id, row_id and timestamp are the same.

Table Defintion

Assuming my table created as follows. (no additional index)

CREATE TABLE table_name (
      [id]         BIGINT IDENTITY(1,1) PRIMARY KEY,
      [session_id] BIGINT   default @@SPID,
      [row_id]     BIGINT   default 0,
      [timestamp]  DATETIME default CURRENT_TIMESTAMP,
      [action_id] SMALLINT NOT NULL,
      [value1]     NVARCHAR(max) NULL,
      [value2]     NVARCHAR(max) NULL,
      [value3]     NVARCHAR(max) NULL)

Select Statement

My current query is:

SELECT d.*
FROM [table_name] d INNER JOIN [table_name] i
   ON d.[session_id] = i.[session_id]
   AND d.[row_id] = i.[row_id]
   AND d.[timestamp] = i.[timestamp]
WHERE  d.[action_id] = 12
   AND i.[action_id] = 11
   AND i.[id] = X -- where X is the input

Questions

I want the query to be as fast as it can, so my questions are:

Is there a difference if I move the where condition in the joins?

SELECT d.*
    FROM [table_name] d INNER JOIN [table_name] i
       ON d.[session_id] = i.[session_id]
       AND d.[row_id] = i.[row_id]
       AND d.[timestamp] = i.[timestamp]
       AND d.[action_id] = 12
       AND i.[action_id] = 11
    WHERE  i.[id] = X -- where X is the input

Should I add an index for the timestamp, row_id and session_id columns?
What will happen first, JOIN the tables, or filter results by WHERE?
is the following query is better?

SELECT d.* FROM (
   (SELECT * FROM [table_name] WHERE [id] = X AND action_id = 11) i
   INNER JOIN
   (SELECT * FROM [table_name] WHERE [id] > X AND action_id = 12) d
       ON  d.[session_id] = i.[session_id]
       AND d.[row_id] = i.[row_id]
       AND d.[timestamp] = i.[timestamp]
 )

What else can I do to make it run faster?


texts: How to check if all the extendend stored procedures (xp_) are enabled or disabled?

texts:
These are the stored procedures that I want to check if they are enabled or disabled.

xp_availablemedia
xp_cmdshell
xp_deletemail
xp_dirtree
xp_dropwebtask
xp_enumerrorlogs
xp_enumgroups
xp_findnextmsg
xp_fixeddrives
xp_getnetname
xp_logevent
xp_loginconfig
xp_makewebtask
xp_regread
xp_readerrorlog
xp_readmail
xp_runwebtask
xp_sendmail
xp_servicecontrol
xp_sprintf
xp_sscanf
xp_startmail
xp_stopmail
xp_grantlogin
xp_revokelogin
xp_logininfo
xp_subdirs
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumkeys
xp_regenumvalues
xp_regremovemultistring
xp_regwrite

I found a code for xp_cmdshell which works perfectly but not sure on how to use it on the other xp_

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  sys.configurations
WHERE  name = N'xp_cmdshell'

It will output zero if it is disabled and 1 if it is enabled. How can I use this on the other xp_ ?


texts: MySQL does not read user specific options in ~/.my.cnf

texts:
I want the server to run an initial SQL query every time it starts:

[mysqld]
init-connect='SET NAMES utf8mb4'

and I want it to be only user specific not globally. so I created a .my.cnf in my user's home directory(~/.my.cnf) but when I try to see if it has affected the server it does not show my added setting and it seems that my settings are being ignored by the server:

    mysqld --print-defaults
------------------------------------
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock
--port=3306
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--skip-external-locking
--bind-address=0.0.0.0 --

key_buffer_size=16M
--max_allowed_packet=16M
--thread_stack=192K
--thread_cache_size=8
--myisam-recover-options=BACKUP
--query_cache_limit=1M
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--max_binlog_s

ize=100M
--default_password_lifetime=0

also this is the result of  SHOW VARIABLES LIKE 'character_set_%'; :

I want theses params to be set to 'utf8mb4' wach time the server boots.

character_set_client
character_set_results
character_set_connection

also I should say that I use Laravel Vagrant as my development environment


texts: Retrieve data from two tables where table 2 has multiple entries for one value

texts:
I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures:

Table 1           | Table 2
------------------+----------------------------------
Table1.Customerid |
                  | Table2.Customerid | Table2.Userid
                  | Table2.Customerid | Table2.Userid

Basically, I have in table 2, multiple records showing assignment of multiple userids to one customerid. On the page this data displays, I'm getting duplicate Customer IDs because there are multiple entries for that customer id in table 2.

How do I display the customer ID only once even if there are multiple entries in table 2?

Here is a sample of the query currently being executed with an additional search parameter in place for the company name:

SELECT '' as campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *
FROM customers C
LEFT OUTER JOIN customer_users CU on C.customerid = CU.customerid
WHERE C.customerid > 0
AND (C.company like '%blue%' OR c.altcompanyname like '%blue%')
AND status = 0
GROUP BY
    c.customerid,c.company,c.firstname,C.lastname,C.address1,C.address2,
    C.city,C.state,C.zip,C.country,C.phone,C.fax,C.cellphone,
    C.reference1,C.reference2,C.groupid,C.email,C.residential,C.status,
    C.datecreated,C.campaigndays, C.campaignstartdate, C.campaignenddate,
    C.lastupdate, C.lastupdateuser, C.campaignactive, C.customerstatus,
    C.regionid, C.categoryid,C.viewed,C.altcompanyname,C.url,cu.id,
    cu.customerid,C.lockaccount,cu.userid, cu.request, cu.regionid,
    cu.primaryrep, cu.dateadded
ORDER BY C.company ASC

What I am looking for is this; no matter how many entries any customerid has in the customer_users table, I only want the distinct customer id's to be returned from the query. When a user searches the customers table, I don't want duplicate records in my gridview just because there are multiple users assigned to the customer.


texts: Is it okay to stop my repair on my mysql database if it's not showing in processlist?


texts: How do I add/remove tables to a Windows Azure database?

texts:
I have a Windows Azure database which I wish to add/remove tables to, and yesterday I witnessed a colleague do this online, on what looked like a web based SQL management studio, but I can't find it anywhere.

I can see a Manage button in the toolbar at the bottom of the page, but all this seems to do is refresh the page. How do I reach the web based management console I saw my colleague use yesterday?

I also tried to connect to the database using Visual Studio's server explorer, but when I try to add a table, I get this helpful error:


texts: 'OraOLEDB.Oracle' provider is not registered on the local machine - Oracle Database Client 12C

texts:
I have some problem with connect to the Oracle Database by Oracle Client. I installed on laptop oracle client - version 32 bit. I can connect to the database from laptop when i use 64 bit applications, but when i will use 32 bit application i get following error:

 ERROR System.InvalidOperationException: The 'OraOLEDB.Oracle' provider is not registered on the local machine.
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at EconLib.Utility.SqlDataAccess.OpenConnection(String host, String database, String user, String pass)

i have seen this question 'https://stackoverflow.com/questions/12903366/oraoledb-oracle-provider-is-not-registered-on-the-local-machine', but i can't find oraOLEDBXX.dll on my laptop. Any idea what can i do ?


texts: Do isolation levels only apply to SELECTS and not UPDATES?

texts:
Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:

1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete
2) 0:02 Thread B runs a query that returns the same 1000 rows
3) 0:05 Thread A updates the last 1 rows in this result set and commits them
4) 0:07 Thread B's query returns*

Depending on the isolation level, the result set in #4 will either contain Thread A's changes or it won't. Is the same true for UPDATES?

The following is an example scenario:

Thread A: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5
Thread B: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5

If both Thread A and Thread B enter their transactions at the same time, and Thread B performs its update after Thread A, will Thread B's update fail to update any rows or will it "see" the record with version 5 and therefore overwrite what Thread A wrote?

Does it depend on the database? e.g. Oracle vs MySql vs PostgreSQL?


texts: Truncated 200GB table but disk space not released

texts: I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.


texts: Are there any other ways to get results from Two tables without using Sub Query

texts:
I have a Customer table

+--------+---------+
| Id     |  Name   |
+--------+---------+
| 1      |   A     |
| 2      |   b     |
| 3      |   c     |
| 4      |   d     |
| 5      |   3     |
| 6      |   f     |
| 7      |   g     |
+--------+---------+

and an order table

+-----+------+--------------------------+
| ID  | C_Id | OrderDate                |
+-----+------+--------------------------+
| 1   | 1    |  2017-05-12 00:00:00.000 |
| 2   | 2    |  2017-12-12 00:00:00.000 |
| 3   | 3    |  2017-11-12 00:00:00.000 |
| 4   | 4    |  2017-12-12 00:00:00.000 |
| 5   | 1    |  2017-12-12 00:00:00.000 |
| 6   | 2    |  2017-12-12 00:00:00.000 |
| 7   | 3    |  2017-12-12 00:00:00.000 |
| 8   | 4    |  2017-11-12 00:00:00.000 |
| 9   | 2    |  2017-06-12 00:00:00.000 |
| 10  | 3    |  2017-07-12 00:00:00.000 |
+-----+------+--------------------------+

I need the result of orders with customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I have this query which returns the result perfectly.

SELECT C_ID , MONTH(OrderDate) from [Order] WHERE MONTH(OrderDate) <> MONTH(GETDATE()) - 1 AND C_ID NOT IN
(SELECT C_ID FROM [Order] WHERE MONTH(OrderDate) = MONTH(GETDATE()) - 1)

Can anyone help me to write this query without using subquery

For better clarity, assume the Orders table contains current year orders only and I need to exclude the customers from the result(get all orders for current year) if they had any purchase in November. Which means if the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November..

For learning purpose I need to know how can we achieve the above in other ways without using sub query.


texts: Are nonclustered indexes used when joining tables?

texts:
I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them.

But I've read recommendations to noncluster index all FKs, I believe that's for helping joining.

Is there then some type of join that uses nonclustered indexes?


texts: Cpu% usage alert is not working for 'alert if counter=raises above'

texts:
I am thinking to create an alert for Cpu usage when it crosses certain value. So i configured one alert for this and attached those screen shots here

But the above alert is not working mail is not triggering when it crosses 50%. Is any thing wrongly i configured?
And same thing i have tested with Alert if counter='falls below' then mail was triggered and it worked perfectly.
Please help me in this any thing i want to change.


texts:
For the role db_denycustomer, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:

DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer

...and it works fine. Cool! But, why?

What I've read in related articles is that permissions stack, but DENY takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY hides the code column too.

Could you please elaborate on this?

I have also included the default db_datawriter and db_datareader roles to the user that I tested with.


texts:
I just built a test cluster with 2 MS SQL Server 2014 machines, 1 domain controller, an 1 file share witness. (All of them are Windows2016-based.)
The 2 MS SQL Server machines are a WSFC members.
When I created the Always On Availability Group, all steps succeeded except creating a listener.
If I try to repeat adding a listener to this AAG, I receive a message:

Cluster network name resource 'myaag_mylistener' failed to create its
associated computer object in domain 'mydomain.lan' during: Resource
online.
The text for the associated error code is: Access is denied.
Please work with your domain administrator to ensure that:

The cluster identity 'MYWSFC$' has Create Computer Objects permissions. By default all computer objects are created in the same
container as the cluster identity 'MYWSFC$'.
The quota for computer objects has not been reached.
If there is an existing computer object, verify the Cluster Identity 'MYWSFC$' has 'Full Control' permission to that computer object using
the Active Directory Users and Computers tool.

What I tried:

Create a computer object: mydomain.lan/Computers/mylistener and give the mydomain\MYWSFC$ "Full control" on it;
Remove the object mydomain.lan/Computers/mylistener and grant the mydomain\MYWSFC$ account permissions to create computer objects within mydomain.lan/Computers (List contents, Read all properties, Read permissions, Create computer objects);
In ADSI Edit, DC=mydomain,DC=lan: ms-DS-MachineAccountQuota raised from 10 to 15 (although there are only 5 machine accounts including mylistener$).

What could still be wrong?


texts:
I have 2 PCs at home: Win10 with SQL Server 2016 and Win7 with SQL Server 2014.

Both servers are installed in Windows Authentication mode. I have set up users with the same name on both PCs.

I can connect to Database Engine from Win10 to Win7 and vice versa, but when I try to connect to SSAS, I get following error:

  Cannot connect to XPSWin10.

  ADDITIONAL INFORMATION:

  A connection cannot be made. Ensure that the server is running.
  (Microsoft.AnalysisServices.AppLocal.AdomdClient)

  Unable to read data from the transport connection: An existing
  connection was forcibly closed by the remote host. (System)

  An existing connection was forcibly closed by the remote host (System)

I was able to connect to SSAS before I reinstalled my Windows 10 PC.

I should say, I have 2 users in each PC (User10 and User7). On Win10 I login with User10 and on Win7 I login with User7. Both users on both PC have the same password.

The service is running - I can connect locally.

I disabled the firewall and still I have the issue. The only difference is it shows me the error after a minute not immediately.


texts: finding the most recently updated record with multiple date columns


texts: Can we set the fixed size for both plan cache and data cache?

texts: As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?


texts: How should I config the my.ini(or my.cnf) file for mysql?

texts:
Thanks for taking your time to look at my question, and I hope that you can help me with this.

I have a Windows Server 2012 server, it has:

Memory : 3840MB Ram

Processor: Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz (2 CPUs), ~2.8GHz

But still mysql is going up to 100% cpu usage I ran the MySQLInstanceConfig.exe configuration Wizard to help me config everything for mysql and this is the my.ini config file that it created:

[client]

port=3306

[mysql]

default-character-set=latin1

[mysqld]

The TCP/IP Port the MySQL Server will listen on
port=3306

basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/data/"

character-set-server=latin1

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=192M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=384M

key_buffer_size=332M

read_buffer_size=64K read_rnd_buffer_size=256K

sort_buffer_size=256K

* INNODB Specific options *
innodb_additional_mem_pool_size=14M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=7M

innodb_buffer_pool_size=643M

innodb_log_file_size=65M

innodb_thread_concurrency=8

But Something is still wrong, it's taking to much CPU


texts: How to copy a SQL Agent Job on same Server?

texts:
I want to make a few duplicate copies of an existing SQL Server Agent Job on the same server and edit them to do the same action on different databases that exist on the same server.

These are multiple step agent jobs which would be a bit more work to recreate manually. I know I can create a script from an agent job to run on a different server, but I don't think that will work on the same database server. How can I achieve this without recreating the agent job from scratch and without replacing the existing job?

Ultimately I want to end up with about 5 duplicates of the original job...


texts: Display column title as in the query without 'AS' statement

texts:
I query data from multiple tables using JOIN statements like this :

SELECT u.id,u.name,d.id,d.name FROM user u RIGHT JOIN dog d ON... ;

When I get the answer, in psql for example, I've got this kind of rows title :

|  id   | name  |  id  |  name  |
+-------+-------+------+--------+

Instead, I'd like to get the original and precise notation :

|  u.id   | u.name  |  d.id  |  d.name  |
+---------+---------+--------+----------+

For now I have to use AS statements, but it's quite repetitive to re-write and it add length to the query and makes it less readable.

Is there a way to get this result with a SQL keyword or with a DBMS parameter?

I searched but wasn't able to find anything on this specific problem.


texts:
Evening guys and gals, I am hoping some of you legends might be able to be help me out here :)

I last used SQL Server back on version 7 back in the dark ages (we rubbed two sticks together to get it going). Now here I am in in the 21st century and would like to come back to DBA work again.

I would like to setup a 'cheap' virtual machine & instance to play with, then work towards the newest certification (I see the newest will commence April 2017 which is good timing for me I think). I am particularly interested in the BI side of things.

Can anyone recommend a good hosting provider for a virtual machine? Do I simply get a Windows Server and go from there or can I get a package somewhere? Azure I assume or elsewhere? I suspect there are a number of options, hoping you can share some good ones :)

Plus any other helpful tips are most welcome, cheers!


texts: PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

texts:
I am working on a financial software where we have huge table of ticks with more than 800M rows.

A single row: symbol_id, broker_id, date, time, bid, ask

The problem is the table has many ticks with identical first four columns and different bid and ask.

What is the best way to update the table and combine such rows into one by taking an average of bid and ask columns?

Example:

|1|2|2012-01-01|12:00:00|1.3456|1.3678|

|1|2|2012-01-01|12:00:00|1.3463|1.3612|

Result row:

|1|2|2012-01-01|12:00:00|1.3459|1.3645|


texts: How to setup vault and Postgres in Google cloud to have the correct permissions?

texts:
The Problem

I am trying to setup Hashicorp vault and Postgres in Google Cloud.

I am using liquibase to manage the schema and when that does a migration, it retrieves a username/password from vault and then runs under that user to perform the migration.

However, the default postgres user, or any other user that tries to use that table can't and receives the ERROR:  permission denied for relation.

If I understand correctly, the users are being created while logged into as the role of postgres. This all works locally but the google cloud stuff seems to be setup in a vastly different way.

Here are some outputs to see what the database is setup as:

\du
                                                List of roles
            Role name             |                         Attributes                         |      Member of
----------------------------------+------------------------------------
------------------------+---------------------
 cloudsqladmin                    | Superuser, Create role, Create DB,
Replication, Bypass RLS | {}
 cloudsqlagent                    | Create role, Create DB
| {cloudsqlsuperuser}
 cloudsqlreplica                  | Replication
| {}
 cloudsqlsuperuser                | Create role, Create DB
| {}
 postgres                         | Create role, Create DB
| {cloudsqlsuperuser}
 test                             | Create role, Create DB
| {cloudsqlsuperuser}
 v-token-power-watc-p079t4r13s85w | Password valid until
| {cloudsqlsuperuser}

\dt
                            List of relations
 Schema |         Name          | Type  |              Owner
--------+-----------------------+-------+------------------------------
 public | databasechangelog     | table | v-token-power-watc-p079t4r13s85w
 public | databasechangeloglock | table | v-token-power-watc-p079t4r13s85w
 public | test                  | table | v-token-power-watc-p079t4r13s85w

The vault creation SQL looks something like this:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}";
GRANT cloudsqlsuperuser to "{{name}}";"

I've tried playing with the roles in a number of ways but it seems all of the new roles that I create are always put into their own sandbox and can't play with any other roles. I'm at a lost at this point of exactly what to do next, it seems that no matter the grant options I can pass, it doens't help.

Current Questions

Is it that the owner needs to be the cloudsqlsuperuser group instead of the temp ownwer?

Is there a way to make that the default instead of having to make sure all my tables are created with the right owner?

Replicate with a local docker instance

Docker Command to start Postgres Database

docker run --rm --name database -v $(pwd)/setup.sql:/docker-entrypoint-initdb.d/setup.sql -e POSTGRES_USER=temp -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb postgres:9.6

Contents of setup.sql

ALTER ROLE postgres RENAME TO cloudsqladmin;
CREATE ROLE cloudsqlsuperuser WITH CREATEDB CREATEROLE;
ALTER DATABASE mydb OWNER TO cloudsqlsuperuser;
CREATE ROLE "postgres" WITH LOGIN CREATEDB CREATEROLE IN ROLE cloudsqlsuperuser;

Creating user under postgres simulating vault user creation

docker exec -ti database psql -U postgres -d mydb -c "CREATE ROLE testuser WITH LOGIN IN ROLE cloudsqlsuperuser"

Create test table from testuser

docker exec -ti database psql -U testuser -d mydb -c "CREATE TABLE test (col1 text)"

Try and select table demonstrating error

docker exec -ti database psql -U postgres -d mydb -c "SELECT * FROM test"


texts: String Function to Get Left 1000, But Only To Last Period or Carriage Return

texts:
I have blocks of text, anywhere between 500 and 2000 characters long.

What I need to be able to do is take a maximum of 1000 characters from the description but only to the last full stop or carriage return (Char(13)), so that the text isn't cut off mid-sentence. Should there be both periods and Char(13)'s I would ideally like it to cut if off at the period.

I'm pasting some randomly generated text to try and show what I mean.

  *Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure.
  Four need spot ye said we find mile. Are commanded him convinced
  dashwoods did estimable forfeited. Shy celebrated met sentiments she
  reasonably but. Proposal its disposed eat advanced marriage sociable.
  Drawings led greatest add subjects endeavor gay remember. Principles
  one yet assistance you met impossible. The who arrival end how fertile
  enabled. Brother she add yet see minuter natural smiling article
  painted. Themselves at dispatched interested insensible am be
  prosperous reasonably it. She boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself
  waited to. Always parish tastes at as mr father dining at. Led ask
  possible mistress relation elegance eat likewise debating.

  Or am nothing amongst chiefly address. The its enable direct men
  depend highly. Ham windows sixteen who inquiry fortune demands. Is be
  upon sang fond must shew. Really boy law county she unable her sister.
  Feet you off its like like six. Among sex are leave law built now. In
  built table in an rapid blush. Merits behind on afraid or warmly. In either so spring wished. Melancholy wayBy message*

I would want to be able to return all text up to "chiefly address." (990 characters).


texts:
Let's start with SQLFiddle http://www.sqlfiddle.com/#!2/64fc5/1

I have some urls and I am making some tasks (crons) related to them. Task can be successful or not. I want to try to do it only three times but not at once, so I've created following query to fetch urls for try to accomplish given task

SELECT
    urls.id, urls.url
FROM
    urls
        LEFT JOIN
    crons ON crons.cronable_id = urls.id AND crons.cronable_type = 'render' AND crons.date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY IFNULL(crons.cronable_id, urls.id)
HAVING bit_or(crons.successful) = 0 AND count(crons.cronable_id) < 3
LIMIT 30

The query is joining crons table to urls table filtering by crons already performed and checking whether each cron was unsuccessful less than three times and never positive either.

The crons table is bigger than urls, because each url has multiple tasks to do. I remove old task regularly, obviously. The query take about 1-2 seconds to get results and I am going to run it every 1-5 minutes. Is there any way to make it faster? I've already tried union statement but it was a lot slower. Joins on those tables are really expensive.

Explain from MySQL (small data set)

id  select_type     table   type    possible_keys   key                                  key_len    ref     rows    Extra
1   SIMPLE          urls    index   NULL    urls_url_unique                              767    NULL    126     Using index; Using temporary; Using filesort
1   SIMPLE          crons   range   cronable_type_date_cronable_id_successful            770    NULL    1   Using where; Using index

Explain from Clustrix (production, large data set, fast growing) - 400ms

Operation                                               Est. Cost   Est. Rows
row_limit LIMIT := param(0)                             585663.68   30.00
 filter (0.expr1 < param(1)) and (0.expr0 = param...    585663.68   4293.00
   hash_aggregate GROUPBY((1 . "id")) expr1 := co...    585472.88   4770.00
     nljoin                                             578774.88   4770.00
       index_scan 1 := urls.__idx_urls__PRIMARY         2865.90     4770.00
       outer                                            120.74      1.00
         filter (2.date > sub_date_interval(cast(...    120.44      0.12
           nljoin                                       119.53      22.67
             index_scan 2 := crons.cronable_type,...    17.50       22.67
             index_scan 2 := crons.__idx_crons__P...    4.50        1.00


texts: Always-on Avaialbility Group Secondary replica NOT identical to Primay

texts:
Can I have Always-on availability group primary node not identical to secondary?
example:
Primary Replica
RAM 64, Cores 8
Secondary Replica
RAM 256, Cores 32

As I estimate read-only queries to be 4 times writes, and I will use read routing to secondary I found a recommendation to have both always-on replicas to be comparable, and I don't know if it is a recommendation, or it means identical Thank you


texts:
I am using Ola's INDEX Maintenance solution. Few indexes are not being re-organised after running job.Below is my code

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium =
   'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationLevel1 = 5,
   @FragmentationLevel2 = 30,
   @UpdateStatistics = 'ALL',
   @OnlyModifiedStatistics = 'Y',
   @LogToTable = 'Y',
   @PageCountLevel = 0

Few indexes which has fragmentation level 27 % still doesn't get re-organised even after running Index-optimize job.

I have went through frequently asked questions, it suggests about pagecountlevel which is already 0 in my case.

Also it has index_id =1 rather than 0.


texts: Able to connect with connection manager but SSIS package does not authenticate

texts:
I have an SSIS package that connects MSSQL2008 server to MSSQL2000 and pulls data off the 2000 server and transfers it to the 2008 server. The 2000 server was recently virtualized and since then the SSIS package will not authenticate.
I can see both servers with OSQL -L/sqlcmd -L. All services, agents and browsers are running.
The SQL user that runs the SSIS package can connect via Test Connection button in the Package Explorer>Connection Manager, but when I attempt to execute the package the error output indicates that the login failed, the password does not match. Error 18456 Sev 14 State 8. I am sure the password is correct as I just created the new SQL user to test. Am I missing something that the SSIS package may authenticate somewhere else?
Should the connection string include the password, or does that get pulled form elsewhere in the SSIS package?
I am not a DB admin/SQL guy so please excuse my green-ness if I am overlooking something simple here, which I hope I am. Thanks for any input


texts: How to show specific type record first in mongodb?

texts:
I want to show result in specific order. I have two categories on request instant and want to show instant record first.
Here is my data:
{
    "_id" : ObjectId("5f34d12d269ab90441440285"),
    "bookingPolicy" : "instant"
},
{
    "_id" : ObjectId("5f34d12d269ab90441440286"),
    "bookingPolicy" : "on request"
},
{
    "_id" : ObjectId("5f2d66490e41163dc558bc0d"),
    "bookingPolicy" : "instant"
}

I want to show instant record first with _id sort.


texts: Is it correct to use IP addresses (instead of hostnames) while configuring Mongo DB cluster? Does it cause any issues?

texts:
I have been using Mongo DB Cluster for some data analysis (Mongo DB version 2.4.5).

My configuration is as follows:

1) Node1 - mongoconfig server, mongod shard

2) Node2 - mongoconfig server, mongod shard

3) Node3 - mongoconfig server, mongos

4) Node4 - mongos

All the nodes above have a unique hostname at OS level (output of shell command "hostname")

Is it correct to use IP addresses instead of hostnames while configuring this Mongo DB cluster. There are issues that I am facing due to DNS server and I would prefer to configure the entire cluster (addresses of config servers, shards, etc.) using IP addresses instead of hostnames.

Is this the correct way to configure the cluster or will it cause any issues?


texts: Postgresql requested starting point is ahead of the WAL flush position of this server

texts:
I recently did a move of a VM and didn't pay close enough attention.

The VM container the postgres master. Some time passed between the backup and the move and now I'm getting stuff like this:

ERROR:  requested starting point 14C/93000000 is ahead of the WAL flush position of this server 14C/524BE2E0

The problem is going to be the fact that the slave received more WAL archives in between the time of the backup and the move.

The problem is: how do I fix it?

I've already done a new basebackup, cleaned wal archives with archivecleaner, but restarted the slave just keeps it coming back.

Sounds like slave WAL is ahead of master.

What's the solution here? I can't find it on google. Is there a way to reset this wal flush position? To tell the server to start anew?

EDIT: I ended up fixing it by deleting the slave and redeploying it, having it take the new basebackup as starting point.


texts: What's a good use case for SELECT * in production code?

texts:
Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object).
But I ran across a case now where I'm tempted to use it but would feel cheap if I did.

My use case is inside a stored procedure where a local temp table is created that should always match the underlying table used to create it, whenever the stored procedure runs. The temp table is populated much later on, so a quick hack to create the temp table without being verbose would be SELECT * INTO #TempTable FROM RealTable WHERE 1 = 0 especially for a table with hundreds of columns.

If the consumer of my stored procedure is agnostic to dynamic result sets, then are there any issues with me selling my services to SELECT *?


texts: Why is this query on JSON values in SQL Server using an index scan instead of an index seek?

texts:
I have a table with the following schema:
CREATE TABLE [dbo].[Obj1Json](
    [ObjectID] [int] IDENTITY(1,1) NOT NULL,
    [PointerToSource] [nvarchar](255) NULL,
    [CreateDate] [datetime2](7) NOT NULL,
    [ModifiedDate] [datetime2](7) NOT NULL,
    [Indexes] [nvarchar](max) NULL,
    [vAccountID]  AS (json_value([Indexes],'$.AccountID'))

where the Indexes column contains JSON that looks like:
{
  "AccountID": 73786,
  "AccountName": "5869b4e9-f441-463f-8f6d-93b4f4ff8c75",
  "ProcessLocation": "Start",
  "IsPasswordProtected": true,
  "InvoiceDate": "2020-12-30T09:00:32.8473077-05:00"
}

The vAccountID column is used for an index on the JSON in Indexes:
CREATE INDEX IDX_Obj1Json_AccountID
ON Obj1Json(vAccountID)

The table has about 10.5 million rows of randomly generated data (all data in the Indexes column has the same structure, however). The query I'm running is
SELECT JSON_VALUE(Indexes, '$.AccountID')
  FROM [Obj1Json]
  WHERE JSON_VALUE(Indexes, '$.AccountID') = 69725

which returns 110 results. When looking at the execution plan in SSMS, I see that an index scan is being used, whereas I would expect an index seek to be used instead.

The query returns quickly (under a second), but I'm curious why an index seek isn't being used. Is there any obvious reason why SQL Server is using an index scan instead of a seek?


texts:
my database size is very big around 100gb and when I restore it takes 3 days to restore. I am using amazon ec2.

My question is I want to setup a new slave to MYSQL master, can I take a snapshot of the Master and create a new instance using the snapshot and then enable replication in between.Given that I take log position before the snapshot and my.cnf changes as required. Snapshot is very fast and takes 1-2hours to create a new machine, whereas mysqldump is taking very very long.

I was just worried if replication works using above method, can anyone help please.


texts:
Have query which generate OpenERP ORM. Table have 100k rows.

SELECT "tbl".id
FROM "tbl"
WHERE (("tbl"."active" = 'True')  AND  ("tbl"."is_company" IS NULL or "tbl"."is_company" = false ))
ORDER BY "tbl"."display_name"

With indexes :

    "ix_tbl_pkey" PRIMARY KEY, btree (id)
    "ix_active" btree (active)
    "ix_displayname" btree (display_name)
    "ix_iscompany" btree (is_company)

Query with order by takes 57735.775 ms.
Plan is :

 Sort  (cost=13031.73..13269.13 rows=94960 width=47) (actual time=57711.753..57725.079 rows=94967 loops=1)
   Sort Key: display_name
   Sort Method: quicksort  Memory: 12918kB
   ->  Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=47) (actual time=0.009..57.056 rows=94967 loops=1)
         Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
         Rows Removed by Filter: 623
 Total runtime: 57735.775 ms
(7 rows)

When i try without order by it takes 65.969 ms.
Plan is :

 Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=4) (actual time=0.026..60.782 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 65.969 ms
(4 rows)

With  set enable_sort = off; it takes  1206.157 ms plan is :

 Index Scan using ix_displayname on tbl(cost=0.00..21479.14 rows=94960 width=47) (actual time=29.912..1194.954 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 1206.157 ms
(4 rows)

Any way to optimize it with indexes ? Because we cant change something in ORM .


texts: Set connection timezone pr query in postgresql with jooq and hikari connection pool

texts:
I am not sure whether this topic belongs to stack.
Basically we have the standard

client <-> rest service <-> database

setup but we have a challenge when it comes to data that is sliced up into intervals.
We have quite a lot of rapid sales data where we slice summaries up into hours/days/weeks/months/years and we want to be able to decide from the client side in what timezone that should be done for.
Our database runs UTC and our REST services runs on UTC, but the client requests to get the data in "Europe/Berlin" or something similar.
All our data containing date/time is stored with timezone.
We are thinking that it could be smart to set the timezone for the connection used to fetch the data to keep the queries simple (avoid the "AT TIME ZONE 'Europe/Berlin'") and other hoops we need to do to get the data.
However our connections are shared between requests and due to the connection pool hence there is a potential that the connection is used by another in between setting the time zone and the query for selects.
Is there some way to get around this to ensure it happens only for this query so multiple selects does not override this.
Best Regards


texts:
I've a table named permissions.

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_code` varchar(45) NOT NULL,
  `permission` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It has data such as

+----+--------------+----------------------+
| id |  role_code   |      permission      |
+----+--------------+----------------------+
|  1 | ADMIN        | app.*                |
|  2 | LOC_ADMIN    | app.location.*       |
|  3 | LOC_READER   | app.location.read    |
|  4 | LOC_APPROVER | app.location.approve |
|  2 | EMP_ADMIN    | app.employee.*       |
|  3 | EMP_READER   | app.employee.read    |
|  4 | EMP_APPROVER | app.employee.approve |
+----+--------------+----------------------+

I would like to query this table to get list of fine-grained permissions for a given role_code

For e.g., for role_code 'ADMIN' I would like to get

app.location.read
app.location.approve
app.employee.read
app.employee.approve

Is it possible to write a SQL query to achieve this?

Thanks for your help.


texts: MySQL using 100% of CPU with 1500 active connections

texts:
I'm having a problem with MySQL. I have a system that runs some cron jobs and these create lots of database connections. When it reaches about 1500 active connections the cpu usage goes to 100% but memory stays fine. My server config is:

CentOS release 6.6 (Final)
Model   Intel(R) Xeon(R) CPU E5-1660 v3 @ 3.00GHz
Cores   16
Speed   1278.984 MHz
Cache   20480 KB
Memory 130GB

file my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_connect_errors=100
open-files=64

interactive_timeout=60
wait_timeout=10

max_connections=2000

max_allowed_packet=5M
tmp_table_size=100M
max_heap_table_size=100M

#query cache desativado
query_cache_type = 0
query_cache_limit = 50M

sort_buffer_size=       1M
read_buffer_size= 128K
read_rnd_buffer_size=1M
#join_buffer_size=208M
join_buffer_size=8M
key_buffer_size=20M
myisam_sort_buffer_size=10M

thread_cache_size = 30

key_buffer=100M
open_files_limit=64
default-storage-engine=MyISAM

innodb_file_per_table=1
innodb_buffer_pool_size=35G
innodb_additional_mem_pool_size=80M

query_cache_size=50M

back_log=75
expire_logs_days        = 1
max_binlog_size         = 30M

tmpdir = /var/mysqltmpdir

long_query_time=1
log_slow_queries=/var/log/mysql_slow_queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
bind-address   =  *
port = 3306


texts: How do I configure Oracle DB 11g Enterprise to be able to be accessed from HTTPS PORT?

texts: I'm trying to create a TSA Listener so I could access my Oracle database from HTTPS Port. I already checked edelivery.oracle.com but I found no Web Tier for Windows. Is there any other way for me to configure my TSA Listener? If it's useful, I already have an Apache in my XAMPP but I already have a database in MySQL RDBMS. If possible, I need to configure them to coexist with each other.


texts: Rebuild Index Causing Data File Growth Issue

texts:
I have a database which is 200 GB in size and I have scheduled a Rebuild Index maintenance plan for all databases every Saturday at 2:00 AM.

The job ran for 8 hours and then it failed. I knew while the rebuild operation was in progress, that the database data file size increases.

In my case, the database size got stuck at 655 GB and the rebuild job failed. I have verified the database data file free space which is currently showing 1.5 GB free space.

Can you please help me to reduce the data file size to the previous state? (200+ GB)


texts: Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server

texts:
I am struggling to find any documentation on how SQL Server actually stores a non-persisted computed column.

Take the following example:

--SCHEMA
CREATE TABLE dbo.Invoice
(
    InvoiceID INT IDENTITY(1, 1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES dbo.Customer(CustomerID),
    InvoiceStatus NVARCHAR(50) NOT NULL,
    InvoiceStatusID AS CASE InvoiceStatus
                         WHEN 'Sent' THEN 1
                         WHEN 'Complete' THEN 2
                         WHEN 'Received' THEN 3
                       END
)
GO

--INDEX
CREATE NONCLUSTERED INDEX IX_Invoice ON Invoice
(
    CustomerID ASC
)
INCLUDE
(
    InvoiceStatusID
)
GO

I get that it is stored at the leaf level, but if the value is not persisted how is anything stored at all? How does the index help SQL Server find these rows in this situation?

Any help greatly appreciated,

Many Thanks,

EDIT:

Thanks to Brent & Aaron for answering this, here's the PasteThePlan clearly showing what they explained.


texts: I need to figure out the last time that a function was used by SQL Server. How can I do this?

texts: I work with databases that contain thousands of stored procedures and hundreds of functions.  I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?


texts:
Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value.

As the names indicate, the frequency of data stored in DailyTable is daily, while the frequency of data stored in QuarterlyTable is quarterly.

How can I join the two tables such that I get daily results combining the most recent (point in time) data from each table based on ID and Date?

DailyTable

    ID   |  Date   |    Value  |
---------+---------+------------
    1    |1/1/2010 |     10    |
    1    |1/2/2010 |     15    |
              ...
    1    |3/1/2010 |     20    |
              ...
    1    |4/1/2010 |     30    |

QuarterlyTable

    ID   |  Date   |    Value  |
---------+---------+------------
    1    |1/1/2010 |   1000    |
    1    |4/1/2010 |   2000    |
              ...

Result

    ID   |  Date   |    Value  |    Most Recent Quarterly Value
---------+---------+-------------------------------------------
    1    |1/1/2010 |     10    |             1000
    1    |1/2/2010 |     15    |             1000
              ...
    1    |3/1/2010 |     20    |             1000
              ...
    1    |4/1/2010 |     30    |             2000

I am dealing with financial data, where the daily table stores stock prices, volume etc., while the quarterly table stores financial information such as net income, revenues. The update frequency of quarterly data depends on each company so it is not safe to assume that quarters have 90 days distance.

The start and end date is dependent on the ID. QuarterStart date is the earnings announcement date which is different for each ID. The table has 3,000 IDs. QuarterEnd date is simply next QuarterStart date - 1 business day (in the financial industry, Fiscal and Calendar quarter are two similar but not identical terms. Both are quarters, but calendar year is based on constant date ranges, while Fiscal is based on announcements).

I m looking for a solution that will not require the creation and maintenance of another table.


texts:
Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2.

The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. Most databases are heavy OLTP load.

Storage is 3 RAID1 arrays of SSDs, and one RAID1 array of HDDs. All the heavily used databases have their data and log files on SSDs.

Previously we used a 15GB RAMDisk for tempdb, however during some queries this would run out of space, and the query would fail.

Most of the advice I could find online seemed to suggest that SQL Server would know best how to use the memory and that I should give all the memory to SQL rather than trying to "outsmart" it by putting tempdb on RAMDisk which SQL wouldn't know about, and risk having pages in memory twice (once in SQL's buffer and again on the RAMDisk.

So when we upgraded to SQL Server 2016, I ditched the RAMDisk, and created 8 10GB data files for the tempdb and stuck these on one of the SSD arrays.

Now I'm seeing near constant baseline of 0.5MB/s writes to each of these 8 files, and sometimes pekaing much higher. That's 500GB/day+ of writes to these SSDs, which has me concerned about endurance.

Further, sys.dm_io_virtual_file_stats shows average_write_stall_ms of 150ms+.

Would you recommend putting tempdb back onto a RAMDisk to both aid performance and protect the SSD's from heavy constant writes?

If I did that, ideally I'd like some sort of "oversplill" so that if an odd query here or there needed a large amount of space in tempdb it could use a second file on the SSD instead of the RAMDisk.
The issue here is that, then SQL would start splitting all writes based on free space between the tempdb data file on RAMDisk and the tempdb data file on SSD.
Would auto-shrink maybe be an option here, for encourage it back to exclusively using the tempdb data file(s) on the RAMDisk after a rare query that needed lots of tempdb space?

Any other recommendations or thoughts welcomed!


texts:
This is just an example:

SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM';

I always get an error:

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.#

I have looked at trying to use CONVERT, but I can't see any format that matches the datetime format I have above. Any advice on how I can rectify this?


texts: Why is a max degree of parallelism 1 recommended and used for Sharepoint in SQL Server?

texts:
I am new to Sharepoint, but I used to work with SQL Server a lot.
I realized that Sharepoint changes some of the SQL Server settings to non default values.
One is the max degree of parallelism, why doesn't sharepoint like parallel plan executions?
Also why should I turn off "auto update statistics?
I couldn't find answers to these two questions.
I am on Sharepoint 2013


texts:
This may be a difficult question to answer, but I have a PostgreSQL database running on a Linux server with 1.3 TB of storage.  I'm currently at 73% utilization of storage space with a slow but steady increase of about 1% a month with the occasional uptick due to a large table being added.

I'm taking into consideration that:

PostgreSQL needs some headroom for queries, MVCC
The operating system needs some headroom, for whatever it is operating systems do
Because Linux doesn't "defrag" in the way we think of with Windows, I need to take into consideration that as space shrinks, contiguous space will be at a premium, which will likely impact performance of both the OS and the DB
Things I haven't thought of

My question is at what point do I need to request more space?  My thought was when I hit 80% I should ask for more, but I want to have a good balance of foresight and responsible use of department funds.

Does anyone have a gut feel for at what point I need to start thinking about increasing my allocation?

Oh, and before anyone tells me that space is cheap -- it's not, not when you work in corporate America.  Space is funded based on service agreements, backup/recovery, etc and does not equal what you would pay for 1 TB of storage at Staples.


texts: sp_executesql with user defined table type not behaving correctly

texts:
Problem
Is there a known issue with user defined table types as parameters to sp_executesql?
Set up script
This script creates one each of table, procedure and user defined table type (restricted SQL Server 2008+ only).

The heap's purpose is to provide an audit that yes, the data made it into the procedure. There are no constraints, no nothing to prevent data from being inserted.
The procedure takes as a parameter a user defined table type. All the proc does is insert into the table.
The user defined table type is bog simple as well, just a single column

I have run the following against 11.0.1750.32 (X64)  and 10.0.4064.0 (X64) Yes, I know that box could be patched, I don't control that.
-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- Stored Procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

Problem reproduction
This script demonstrates the problem and should take five seconds to execute. I create two instances of my user defined table types and then try two different means of passing them in to sp_executesql  The parameter mapping in the first invocation mimics what I captured from SQL Profiler. I then call the procedure without the sp_executesql wrapper.
SET NOCOUNT ON
DECLARE
    @p3 dbo.UDTT
,   @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

Results
Below are my results. The table is initially empty. I emit current time and make the two calls to the sp_executesql. I wait for 5 seconds to pass and emit current time followed by calling the stored procedure itself and finally dump the audit table.
As you can see from the timestamp, the record for the B record corresponds to the straight stored procedure invocation. Also, there is no SQLC record.
ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

Tear down script
This script removes the objects in the correct order (you can't drop the type before the procedure's reference has been removed)
-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

Why this matters
The code seems silly but I don't have much control over the use of the sp_execute vs a "straight" call to the proc. Higher up the call chain, I am using the ADO.NET library and passing in a TVP as I have done previously. The parameter's type is correctly set to System.Data.SqlDbType.Structured and the CommandType is set to System.Data.CommandType.StoredProcedure.


texts: How to easily keep versioned data in an Oracle table

texts:
I currently have some data stored in a table in an Oracle 12c database. To simplify, it has a K column (the PK) and a D column (some data).
I would like to store versioned data :

Add a V column to the table, representing a version of the data associated to the key K.
As such, extend the PK to be (K,V)
In my application, use a view (in place of the table) that selects (K,V) where ROW_NUMBER() OVER (PARTITION BY K ORDER BY V DESC) = 1
Upon insert, insert the System Change Number into the V column.

Is the SCN ever-increasing in an Oracle database? Is there a better solution than this?


texts: Importing larger CSV files to an empty table in a local MySQL database

texts:
I am building a local database made up of a handful of tables. The first few worked fine using the Export/Import option on the window that comes up after right clicking on a table and selection "Select Rows = Limit 1000."

Unfortunately, once I got to a table that has 30,000 rows, the feature stopped working. MySQL Workbench crashes after every attempt. The import/export wizard is also unhelpful.

I am reading the documentation on using the INLINE command (https://dev.mysql.com/doc/refman/5.7/en/load-data.html), but it is not clear to me where to actually input the command. What command line tool do I use? Is there a better way to import data? Any advice would be highly valued for a new user.


texts: ORA-04031: unable to allocate X bytes of shared memory

texts:
Trying to start oracle service, shows me the follow error:

I have the follow SPFILEXE.ORA:

Im using ORACLE XE, but really I don't know which parameter change and what should be the value.

Please help !!! Thanks in advance