texts:
i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a function that receives the ItemID and then multiplys the discount price found in fnDiscountPrice() with the same ID by the quantity. Code shown below.

CREATE FUNCTION dbo.fnItemTotal
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT ItemID, SUM(dbo.fnDiscountPrice(@ItemID) * Quantity) AS TimeTotal
        FROM OrderItems
        GROUP BY ItemID)

I keep getting an error of "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnDiscountPrice", or the name is ambiguous."


texts: SQLAlchemy connection error with Postgres

texts:
I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name, it throws the following error:

  OperationalError: (psycopg2.OperationalError)
  FATAL:  no pg_hba.conf entry for host "fe80::19e5:a9a0:bcc2:1c5b%11", user "postgres", database "postgres", SSL off

I checked pg_hba.conf, and here's what I have:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust

host    all             all             0.0.0.0/0               md5

I am able to connect to this Postgres database using other tools, like PgAdmin from a different machine, Tableau, Talend, etc.

UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.


texts: How to speed up rebuilding a database from scratch/scripts

texts:
Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on.
When needed, we can rebuild the entire dev-database by starting a powershell script which will run through every script by feeding it to sqlcmd.exe.

This process recently took about 30 to 40 minutes. We discovered that we were using the old 2008R2 Version of sqlcmd, thanks to a mistake in the local path variables, which pointed to the old version.
After fixing this the entire process was reduced to about 8 minutes.

I am now wondering if there are any "switches" I might have overlooked which could speed up this process even more.

The Database is currently set to compat level 100 (for stupid reasons), multi_user mode and simple recovery model. The database runs locally and no user will be connecting during the rebuild process.

I did try setting it to single_user mode but ran into an issue. Apperently the sequential process of firering one sql-script after another seems to be too quick(?) for it's own good. Somehow one connection is not closed fast enough before the next one is established.

Are there any other options either on query or database level that could speed up a database rebuild process in this scenario?


texts: Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

texts:
I'm using IMPDP with the "network_link" for perform export/import in a single-pass.

It runs fine until I get this error.

  ORA-39171: Job is experiencing a resumable wait.
  ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Here are the datafiles for the UNDO tablespace:

oracle@ORA3> select bytes/1024/1024/1024, maxbytes/1024/1024/1024
             from dba_data_files
             where tablespace_name like '%UNDO%';

Output:

BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024
-------------------- -----------------------
          31.9999847              31.9999847
          31.9999847              31.9999847
          31.9999847              31.9999847
                   2              31.9999847
                   2              31.9999847
                   2              31.9999847

So I have around 196G of undo space. I added the last three data files after the error.

The largest tablespace is 674G. Do I need to make the UNDO tablespace larger than this? Or is there something else I should do?


texts: Amazon RDS - Can't access MySQL DB from local machine

texts:
I read many questions/answers here but they are from a different interface from AWS Console. I created a DB Instance but can't create a rule in the security groups that's allows me to connect from my computer. I tried these rules :

All tcp with my IP
All traffic with my IP
MySQL with my IP

By the way I'm using MySQL Query Browser

Anyone knows the right rule to use ?


texts:
I'm having this question regarding a restore scenario in RMAN:

A database is running in Archivelog mode. Full RMAN hot backups are taken, redologs are backed up with RMAN to tape. The application remains online while any backup is taken.

Enter this scenario:
01h30 Redologs are backed up to tape using RMAN
    02h00 a Full backup starts
    08h00 the Full backup ends (and it was 100% successfull)
    08h05 ALL Redologs on disk, including multiplexed copies, are permanently lost. Additionally, one (1) regular datafile is lost.

Note the backup itself took a long time : assume that is normal. During that same time, there was activity (as stated above, the application was running), but there really was not much happening. Still, redologs were written in that period (the same redologs that were lost).

And this is the question I'm wondering now :  If we just restore the complete database, on purpose not specifying any time to restore to, it is correct to say the the restore will restore to a point in time, just after 02h00 ?


texts: How can I switch to sa account from guest session in SQL server 2012?

texts:
I have a situation where I can log into my server as guest, and see that there is no password on the sa account.  However when I try to login as sa with null password over the network it is rejected.

My question is, since there is no sa password, how can I do the equivalent of a unix sudo or su command, while logged in as guest?  I looked into runas and changing passwords but guest lacks the very privilege I am trying to gain.  ('alter login sa..' doesn't work.)

Also, why does the server refuses login when there is no sa password?

This is SQL Server 2012 SP1 Web edition 64-bit 11.0.3153.0.

For example, there is a command in MySQL called change_user. This command can be used as the name suggests to change a user during a MySQL session. There may be a way to do this in SQL Server but I am not a SQL Server expert so I seek more experienced input.


texts: What is the easiest way to use a SQL Server backup to retrieve just a few rows?

texts:
I need to manually retrieve a few rows from a SQL Server 2008 database backup. Normally I create a new database, restore the database backup to the new database, and run my queries. The database is huge, however, so it takes forever to restore.

Is there an easier way to get at the older data?


texts:
I want to make a column in a relation unmodifyable for consistency reasons.

The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that anyone is able to change the IDs of the relationship partners.

What I have come up with so far:

Creating a trigger that checks if the NEW value is the same as the OLD value. But I don't know how to deny the UPDATE action if the condition proves as TRUE.

Here is my trigger so far:

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse
FOR EACH ROW BEGIN
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN

    END IF;
END;


texts: mysql dump import incredibly slow on my developer's machine

texts:
I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in 40 minutes)
So this is factor 53 slower.

The specs:

Server A:
   MySQL Version: 5.5.30-1.1 (Debian)
   2 GB RAM
   1 core QEMU Virtual CPU version 1.0 - cpu MHz: 3400.020

Workstation B:
   MySQL Version: 5.5.41-MariaDB-1ubuntu0.14.04.1
   14 GB RAM
   4 cores Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz - cpu MHz: 1600.000

The mysql/maria config is the stock config.

I switched yesterday to MariaDB on my workstation - but before MariaDB the stats were even worse.

I already removed all databases on my workstation - no difference.

The big question is: How can the performance be factor 53 slower? I can not work like this :-(

My import command:

pv sql/master.sql | mysql -h'localhost' -u'root' -p'root' 'master'

iostat -xm 5

server A:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
      17,43    0,00   30,28   51,85    0,00    0,44

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00   254,03    0,00 1305,45     0,00     6,09     9,56     0,78    0,60    0,00    0,60   0,57  74,25

workstation B:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
       7,32    0,00    3,22    5,03    0,00   84,42

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     1,40    0,80  172,40     0,00     0,56     6,72     1,17    6,75   12,00    6,72   5,40  93,52

dd if=/dev/zero of=tempfile bs=1M count=1024 conv=fdatasync,notrunc

server A:

1073741824 bytes (1,1 GB) copied, 18,6947 s, 57,4 MB/s

workstation B:

1073741824 bytes (1,1 GB) copied, 8,95646 s, 120 MB/s


texts: RMAN-06169, but only when backing up via OEM 13c

texts:
When running a Level 1 or Level 0 backup via OEM 13c, the following RMAN error causes the run to fail. (The RMAN script is being ran with proper sysdba credentials from OEM 13c.)

RMAN-06169: could not read file header for datafile 2 error reason 5

Oracle documentation suggests reason 5 indicates 'Unable to open file'.

However, if I execute the exact same RMAN statement directly on the server, the backup runs fine.

We have removed the database from OEM and decommissioned the agent, the redeployed the agent as well as reinstated the database in OEM, but the error still occurred.


texts: Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

texts:
Basic setup:
All of the following is installed on Domain\Servername:

SharePoint 2013 Standalone Install (Trial version)
Currently using an instance of SQL Server 2012 SP1 Express for basic
SharePoint databases (upgraded from SQL Server 2008 R2 Evaluation).

Also using an instance of SQL Server 2012 SP1 Evaluation for
Reporting Services for Sharepoint (Express instance doesn't support
Reporting Services for Sharepoint Integration and Express instance
couldn't be upgraded to Evaluation)

Problem:
Ok, so I'm going through the steps to Install Reporting Services Sharepoint Mode for Sharepoint 2013 that can be found at http://msdn.microsoft.com/en-us/library/jj219068.aspx.  When I get to the end of Step 3: Create a Reporting Services Service Application and press OK, I get the following error: Login Failed for user 'Domain\Servername$'.
According to what I've found on the interweb this means that "a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization."
This makes sense since the account that is used for database provisioning for the new Reporting Database that needs to be created behind the scenes as part of Step 3 is the Identity for the Application Pool of SharePoint Central Administration which I currently have set as Network Service.
Anyhoo, the agreed upon solution is to add the login Domain\Servername$ to the SQL Server Instance that's giving me trouble and give it the right permissions ( link for similar problem: http://support.microsoft.com/kb/889646/en-us).
So, I add the login for Domain\Servername$ and give it server roles: dbcreator, public, securityadmin and sysadmin and then just to be on the safe side I give it db_owner permissions for each database in the SQL Server 2012 Evaluation instance I have.
And I still get the same error :S
Just to get some more information I look at the SQL Server Error Logs using Management Studio and I can see that ther error ID is 18456, Severity 14 State 5 and the Reason for the error is  Could not find a login matching the name provided. [CLIENT: ] .
Question
Why am I getting this error if I've already added the login Domain\ServerName$? Any ideas or workarounds?
Thanks!


texts: Do I need client or server cert for mySQL master/master replication?

texts:
WE use pkitool to sign any certificate using our own CA and there are two options to sign a certificates:

using --sign, which creates a client-cert
Using --server, which creates a server-cert

The difference between those two are in X509v3 Extended Key Usage. For #1, it says TLS Web Client Authentication whilst the latter says: TLS Web Server Authentication. My question is: which particular type of cert(s) do I need to do a encrypted master-master replication?

I went through various replications guides but none mention about the type specifically or used the term 'master' and 'client' interchangeably. Can anyone help me out here? Thanks in advance!!

-S


texts: Do my relational algebra operations produce the desired results?

texts:
I have these tables:

        Sailors

sid  sname   rating  age
---  ------  ------  ---
22   dustin  7       45
31   john    8       55
58   ben     10      35

         Boats

bid  bname      color
---  ---------  -----
101  interlake  blue
102  interlake  red
103  clipper    green
104  clipper    red

       Reserves

sid  bid  date
---  ---  ----------
22   104  8/10/2014
22   103  7/05/2014
58   103  8/11/2014
31   102  8/11/2014

I am trying to find sailors, sname and rating, who have reserved both a red and a green boat, and I need to write that in relational algebra notation.

I tried this:

Text version, for copying/referencing purposes:

R1 := Boats ⋈ Services
R2 := σcolor='Red'(R1)
R3 := σcolor='Green'(R1)
R4 := R2 ∩ R3
R5 := R4 ⋈ Sailors
R6 := πsname,rating(R5)

I was told it was wrong, but not given an explanation as to why. Can someone explain to me what the problem is?

This line was marked as wrong:

 R4 := R2 ∩ R3


texts: SQL Server 2005 still running after in place upgrade to sql 2012

texts: We upgraded a default instance from SQL Server 2005 to 2012 today. But Components of SQL Server 2005 are still on the server. These includes the MSQL1. folder (it has all the data and log files), Mgmt Studio and configuration manager. The instance now shows it is 2012 version. I am hesitant to uninstall SQL Server 2005 as it may cause problems. Any thoughts? Has anyone come to experience this? Thanks in advance


texts: Why does score for mysql boolean match give different score?

texts:
I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an integer, but occasionally returns a float, particularly when using optional statements. The table in question is MyISAM.

Example:

SELECT *, MATCH(columna,columnb,columnc)
AGAINST('+"exact phrase" +definitely -negative this that) as score
FROM table
WHERE MATCH(columna,columnb,columnc)
AGAINST('+"exact phrase" +definitely -negative maybe) >= score

In the above statement I'm searching for exact phrase, definitely, not negative, and this or that.

When performing the query we get a varying score depending on the number of search terms used and the operator(and,or,not) being used for each item.


texts: What are implications for changing encryption from TRIPLE_DES to AES_128?

texts:
As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128?

I have an older database I've been tasked with hardening, and I need to updated the algorithm used for encryption on it.

Running this query:

  SELECT name, key_length, algorithm_desc, create_date, modify_date FROM
  sys.symmetric_keys;

returns a result with theh name: ##MS_ServiceMasterKey##, using the TRIPLE_DES algorithm.

I didn't find any asymmetric keys used any of the database on this server.

Thanks for any help!


texts: How are the relations from Relational Database mapped to DataWarehouse?

texts:
this year one of our collage courses was related to creating and managing data warehouses. There is one thing that I really struggle to understand and I would appreciate any kind of advice that you have.
My question would be: How does the original relations from relational database map to fact and dimension tables of the data warehouse.
In my example I have a simple cinema Database with this structure (has other columns but are not relevant)
ticket
-------
id - primary
price
something...

invoice
--------
id - primary
ticketId - forieng key
orderId - foriegn key
totalPrice

My DWH looks like this (it has more columns but they are not important, it's a star schema)
FactInvoice
----------
id - prim
ticketKey - foreign
dateKey - foreign
orderKey -foreign
currency
totalOrderPrice
totalPrice

DimTicket
---------
id - prim
seatNumber
isUsed

DimOrder
--------
id - prim
orderType

DimDate
-------
id - prim
day
month
year
nameOfDay

My problem with this is, what are ticketKey, dateKey and orderKey, are they just references to the autogenerated primary key in dimensions or are/should they be relations from the original relational database ?
For example if I had this in relational database
TICKET - ID:12 price:5
INVOICE - ID:234, ticketId: 12, orderId:123, totalPrice:20

Should the keys in data warehouse represent ID 12 for ticket so that the relation from INVOICE to TICKET exists or should they be independent of the relational model.
Also if they are just references to the autogenerated field in the DW how can I add a reference so I have  a correct relation between single invoice and single ticket.
We are using Visual Studio SSIS extension to create data flow, and for now I have queries that select these information but I don't know if I should import the ID's from those tables.
Sorry if this question has a obvious answer but the whole concept of fact/dimension is pretty new for me and I don't know if I'm understanding it correctly. Thanks In Advance


texts: Storing and querying rolling data in PostgreSQL

texts:
I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a different variety of weather data (temp, dew point, wind, etc.). Each table will have 6-7 columns: latitude, longitude, point geometry, elevation, date-time the model is relevant for, and 1-2 data values of interest. The data will be primarily queried for a bounding box by time and elevation. There will be approximately 145,757,360 rows per table (data older than now is no longer relevant will be deleted). I roughly estimate the size of the tables to be about 10 GB each without indexes. (That's 52 bytes of data plus 23 bytes of overhead per row). The data will be regularly updated/inserted as new model data becomes available. Note: new data will include overwriting existing values in the table.

So I'm looking at these two plans:

Simply index and cluster by (datetime, elevation) with an additional index for the point geometry. Run a regular cron job that deletes old rows, runs vacuum/analyze, and re-clusters.
Partition by datetime and then cluster and index by elevation per table with an index on geometry. Run a regular cron job to add new tables going forward and drops old tables.

Further,

So, I know that dropping a table is much more efficient and deleting and vacuuming. But would I see a performance boost otherwise?
Are partitions appropriate when the all of the tables will be evenly updated and selected on until deleted as irrelevant (the documentation indicated that partitions worked best when only a few of them would be selected on)?

When delivering data will the selects be any faster than the clustered index? Does the answer change if multiple requests are being made at once?

Thank you. I hope I put up all the needed data. If not let me know and I'll add it.


texts: What is wrong in debunking myths about clustered indexes?

texts:
What is wrong in code. or its results, illustrating that clustered index is evil [1]?
and how to de-debunk, i.e. to return to customary myths and best practices?

[1]
Debunking myths about clustered indexes - part 3 (example script)
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/16/377.aspx

Edit by gbn, Jan 2012

The dead link used to have a script that "proved" clustered indexes were bad.

Similar SO question: https://stackoverflow.com/questions/4034076/reasons-not-to-have-a-clustered-index-in-sql-server-2005


texts: Oracle: Tables and Views missing from the all_objects dictionary

texts:
I have a couple of database instances that are having the curious issue where tables and views do not show up in the all_objects dictionary which is causing some issue with certain schema viewing applications. Not all are missing, but there is only like a dozen of the 130 or so tables/views that are in the schema. If you query all_tables or all_views, they all show up just fine. If you select/update/delete with those tables, it works fine.

Any one know of what types of things I should be looking at that would cause this?

BTW, I am not the DB administrator to this DB, I'm just trying to figure this out for our client. I don't have full access to the DB.


texts:
I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible?

Thanks


texts: Query taking more time to execute due to lengthy column size

texts:
When I am trying to execute below query in SSMS, the query is taking more time (more than 10 minutes) to execute due to lengthy column size (Column Name: Keywords, Column Size: nvarchar(4000)). Employee table is containing 6000 records.

Select EmployeeId, EmployeeName, Designation, Keywords
From Employees
Where StatusFlag = 'L'

I have created below non clustered covering index for the above table.

Create NonClustered Index NCI_Employees_StatusFlag On Employees(StatusFlag) Include (EmployeeId, EmployeeName, Designation, Keywords)

How to retrieve the data from the above table in a faster manner?


texts:
I killed a session

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

But it remains in v$session for 10 days, although an attached process is absent in v$processes.

SELECT
    s.username,
    s.osuser,
    s.sid,
    s.serial#,
    p.spid
  FROM v$session s, v$process p
 WHERE s.sid = '123'
   AND p.addr (+)= s.paddr;

I've also checked all Linux processes related to Oracle and found no zombies: all present processes are consistent to alive sessions.

This happens not for all the killed sessions. It seems that PMON is active, because the most of the killed sessions are cleaned from the list in a reasonable time (often it takes a second).

What can be done to eliminate long-playing killed session from the list?


texts:
I have started working on a new database and found something that stands out as abnormal.

I have a table that holds contacts similar to below

contacts
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
|  2 | John        | Doe         | john.doe@example.com |
+----+-------------+-------------+----------------------+

Each user has access to specific users on the website. The current way our system handles this is to duplicate the table and append the users id to the end of the table name, it then only inserts records relevant to the user.

contacts_201
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
+----+-------------+-------------+----------------------+

I always thought in situations like this a link table would be used like this to resolve what is a many to many relationship.

contactMemLink
+------------+--------+
| contact_id | mem_id |
+------------+--------+
|          1 |    201 |
+------------+--------+

Extra information:

There are around 24000 records in the contact table
There are around 100 users on the website
Each user has the possibility of having every user assigned to it.

My Question

Is there any reason on why the database would have been designed this way based on best practices?


texts: How much time will a vacuum/autovacuum operation take?

texts:
I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other few inserts and large number of updates.

Database runs on PostgreSQL 8.4 on a Debian 6.0 amd64 system with 16 gigabytes of RAM.

The question is sometimes autovacuum process on a table, takes a very long time (days) to complete. I want to be able to roughly tell how much time a particular vacuum command will take, to be able to decide whether to cancel it or not. Also if there were a progress indicator for postgres vacuum operations, it would be really helpful.

Edit:

I'm not looking for a bullet-proof solution. Just a rough hint on the number of dead tuples or necessary I/O bytes is enough to decide. It is really annoying to have no clue when VACUUM will finish, whatsoever.

I've seen that pg_catalog.pg_stat_all_tables has a column for number of dead tuples. So it is possible to have an estimation, even if it means one has to ANALYZE the table before. On the other hand, autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor settings alone prove that postgres itself knows something about the amount of change on the tables and probably puts it in the hands of the DBA too.

I'm not sure what query to run, because when I run VACUUM VERBOSE, I see that not only tables, but indexes on them are being processed too.


texts:
Is PostgreSQL 10 available on Ubuntu 17.10 artful?

I checked that the folder on APT actually exists: http://apt.postgresql.org/pub/repos/apt/dists/artful-pgdg/10/

But after I do:

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ artful-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-10

I get an error:

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-10

The above steps worked for Ubuntu 16.04 (with xenial-pgdg), but not for 17.10.

Any pointers?


texts: mysql master and slave has different schema alter table add column

texts:
I have added column in large table it was having some unique index which was causing issue,So i drop index and add column and after adding column i have added unique index.
But all of my slave is broke they don't have master schema,
I am not sure how to solve this problem

ALTER TABLE User ADD COLUMN foo_bar VARCHAR(255);
Table also consist of email which has unique index

MySql Version :  5.6.30,
Master Create Table

CREATE TABLE `gobe_User` (
  `uuid` bigint(32) unsigned NOT NULL DEFAULT '0',
   `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `encrypted_password` varchar(255) DEFAULT NULL,
   UNIQUE KEY `uuid` (`uuid`) USING BTREE,
    UNIQUE KEY `emai_2` (`email`),
     KEY `email` (`email`),
     ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 ROW_FORMAT=COMPACT;

Slave Create Table

    CREATE TABLE `gobe_User` (
      `uuid` bigint(32) unsigned NOT NULL DEFAULT '0',
       `name` varchar(255) NOT NULL,
      `email` varchar(255) DEFAULT NULL,

       UNIQUE KEY `uuid` (`uuid`) USING BTREE,
        UNIQUE KEY `emai_2` (`email`),
         KEY `email` (`email`),
         ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 ROW_FORMAT=COMPACT;


texts:
Been looking to pivot rows (progress_check) into columns check 1, check 2 etc...
No sum or totals required, just to display the results hopefully...
Can anyone help, Thanks Ad


texts: Complex dto based result sets with EF and MS SQL server

texts:
ok lets say I have a collection of these ...

public class ReferenceLookup
{
    public string InvoiceNumber { get; set; }
    public CompanyReference Buyer { get; set; }
    public CompanyReference Supplier { get; set; }
}

I need a collection of these as results where any of the pieces exist ...

public class ReferenceLookupResult
{
    public SIHead SIHead { get; set; }
    public Company Buyer { get; set; }
    public Company Supplier { get; set; }
}

SIHead == invoice

Companies have related tables that contain external references for the company
I somehow (if possible) want to write a single linq query that can do this in a way that exposes me a queryable set of  ReferenceLookupResult if possible,
how I would get these pieces is quite complex though

for example here's how I currently get a collection of companies by reference ...

public IQueryable<Company> ByReferences(CompanyReference[] refs)
{
    var refStrings = refs.Select(r => r.System + "|" + r.Ident);
    return GetAll().Where(c => c.CompanyTaxIdentifiers.Any(cti => refStrings.Contains(cti.ExternalReferenceSystem.DisplayName + "|" + cti.Reference)));
}

... i think i need multiple collections of companies and a collection of invoices where one from each set makes up 1 result dto

here's how I get invoices by reference ...

public IQueryable<SIHead> GetAllByRefs(ICollection<InvoiceReference> refs)
{
    var keys = GetAll().Select(i => new { Id = i.SIHeadId, Number = i.InvoiceNumber, Supplier = i.SupplierCompanyAux.CompanyId }).ToList();
    var resultItemIds = keys.Where(k => refs.Any(r => r.InvoiceNumber == k.Number && r.SupplierId == k.Supplier)).Select(k => k.Id).ToArray();
    return GetAll(resultItemIds);
}

an invoice reference is an invoice number and a supplier reference
all invoices are at their root generated by suppliers.

Options considered so far

Create a sproc and call that (not queryable though I would think)
Create a view and add that to the context (EF Code First treats this like a normal entity set)

I'm thinking the latter might be the way to go, but in the past each time I've had a view for something I've ended up replacing it longer term with something LINQ based.


texts: Windows Service Management Node Won't Start

texts:
I'm trying to set up a basic Cluster on my Win7 PC for testing. But I am stuck at the first hurdle. I have installed the Management Node as a Service:

C:\MySQLCluster\ManagementNode1\bin\ndb_mgmd.exe --install --config-file=C:\MySQLCluster\ManagementNode1\config.ini

But when I try and start the service it instantly stops. Surely as the management node it should run as a service?

What else do I need to do?

I am trying to start it from the Services Window, but I just get

  The MySQL Cluster Management Service on Local Computer Started and then stopped. Some Services Stop automatically if they are not in use by other services or programs

Alternatively from the command line:

NET START ndb_mgmd

  The MySQL Cluster Management Server service is starting.
  The MySQL Cluster Management Server service could not be started.
  The service did not report an error.


texts:
I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application on Linux.  I've had a look at this:
http://go4expert.com/articles/database-transaction-auditing-mysql-t7252
and that looks good because it uses triggers to automatically create the audit records, but it looks as if it will record the MySQL user (e.g. USER() or CURRENT_USER()) in the audit table record, and since my application uses the same MySQL user for all web users, this won't identify the actual person who is making the change.  In my application, the ID of the person making the change is stored in a Perl variable (say $userid, which is an integer).

So, without me having to do extra coding wherever I make changes to tables (presumably by using triggers), how can I get MySQL to audit all changes to my tables, recording the $userid (Perl variable) as the user who made the change?

I've also had a quick several articles, including this one:
http://dev.mysql.com/doc/refman/5.0/en/account-activity-auditing.html
but haven't recognised a solution to my $userid problem yet.

I'm using mysql Ver 14.14 Distrib 5.5.40, for Linux (x86_64) using readline 5.1, and Perl: 5.10.1.

Thanks.


texts: Does SKIP LOCKED FOR UPDATE work with views?

texts:
I am currently using SKIP LOCKED FOR UPDATE to schedule tasks as such:

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM task
  WHERE /* ... bunch of logic ... */
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

I would like to abstract the sub-query used to select outstanding tasks using a view, e.g.

CREATE VIEW outstanding_task AS
SELECT *
FROM task
WHERE /* ... bunch of logic ... */

and then use the view to pick tasks for scheduling, e.g.

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM outstanding_task
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

Does SKIP LOCKED FOR UPDATE work with views in this setup?, i.e. Does it guarantee that the same ID will not be picked?

How to test it to be sure?


texts:
I have a database with multiple schemas and data in the tables. I create an rman backup and backup archivelogs. I drop 90% of the tables. I do rman restore and recovery with controfile from autobackup. Checking database tables, nothing has been recovered?

I noticed that if I remove actual oracle database files(.ctl,.dbf/etc) the rman restores those, but I thought the tables would automatically be restored as well.

Please let me know if more info is needed, new to Oracle and Rman.


texts:
In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2.
Many users access this database via some applications, that sometimes "forget" to commit a transaction, then idle indefinitely keeping the table lock. This has a terrible effect: all other applications queue to obtain a lock on the tables affected, and are effectively stuck.
In Sybase I use a query that tells me which user is causing the problem; I can then either kill the task, or even go to him/her and find and correct the problem in the application.
This is the Sybase query:

select l.spid, SysLogin=s.name,SysObject=o.name, dbname
  from master..syslocks l, master..sysprocesses p, SIAM..sysobjects o, master..syslogins s
 where o.type='U'
   and p.spid=l.spid
   and l.id=o.id
   and p.suid=s.suid

The output produced looks like this:

81  john    authors     maindb
88  mary    authors     maindb
88  mary    books       maindb

Unfortunately, syslocks does not exist in Mssql, at least not in the version I am using.
How do I convert the query to work on Mssql?
Of course, if there is an alternative way of achieving the same result that would be great.


texts: Trigger on PostGIS line layer occurs twice

texts:
Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, as all of the triggers occur twice.

I first realized this because the history table inserts lines for the 'INSERT' and UPDATE logic twice (not for DELETE, surprisingly). The notices print to the console twice for INSERT, UPDATE, and DELETE.

For example:

When creating a line in the base table, the history table creates two objects with the same attributes except for the hid (history table id).
When updating the attribute of an existing line, in the history table, three objects are created:

One with etat = 'MODIFICATION COURANTE' (resembles the existing feature in the base table, including the updated attribute)
Two with etat = 'MODIFICATION ARCHIVEE' -- one with the old attribute value and one with the new attribute value. At this point, I am expecting only one object containing the old attribute. The object containing the new attribute is out if place.

CREATE or REPLACE FUNCTION test.test_track_history_tracker() RETURNS trigger AS
$new_test_track_history_tracker$
BEGIN
  -- INSERT

  IF (TG_OP = 'INSERT') THEN
     INSERT INTO test."Conduite_test_history"
        (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'CREATION', NEW.geom);
     raise notice 'Insert happened';
     RETURN NEW;

  -- UPDATE
  ELSEIF (TG_OP = 'UPDATE') THEN
     UPDATE test."Conduite_test_history"
         SET deleted = current_timestamp, deleted_by = current_user, modified = TRUE, etat = 'MODIFICATION ARCHIVEE'
     WHERE deleted IS NULL and gid_org = OLD.gid;

     INSERT INTO test."Conduite_test_history"
         (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
     raise notice 'Update happened';
     RETURN NEW;
  -- DELETE
  ELSEIF (TG_OP = 'DELETE') THEN
     UPDATE test."Conduite_test_history"
       SET deleted = current_timestamp,
           deleted_by = current_user,
           etat = 'SUPPRESSION'
     WHERE deleted is NULL and gid_org = OLD.gid;
     raise notice 'Delete happened';
     RETURN NULL;
  END IF;
END;
$new_test_track_history_tracker$
LANGUAGE plpgsql;

--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker
  AFTER INSERT OR UPDATE OR DELETE ON test."Conduite_test"
  FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();


texts: Return a specific value based on the min between 2 dates in SQL Server

texts:
I have 2 tables :

CREATE TABLE #Table1(Name varchar(10), Date date, Type varchar(10));
INSERT INTO #TABLE1
VALUES
('aa',    '2018-09-10',  'Client'),
('bb',    '2018-10-12',  'Client'),
('cc',    '2018-10-16',  'Client'),
('dd',    '2018-11-12',  'Client'),
('ee',    '2018-10-15',  'Client'),
('ff',    '2018-11-17',  'Client'),
('gg',    '2018-12-12',  'Client'),
('tt',    '2018-11-11',  'Server'),
('kk',    '2018-12-15',  'Server');

CREATE TABLE #Table2(ID INT, Type varchar(10), DateCreated date);
INSERT INTO #Table2
VALUES
(1,'Client', '2018-11-25'),
(2,'Client', '2018-10-25'),
(3,'Client', '2018-11-17'),
(4,'Client', '2018-09-25'),
(5,'Client', '2018-11-10'),
(6,'Client', '2018-12-05'),
(7,'Client', '2018-11-02'),
(8,'Server', '2018-11-19'),
(9,'Server', '2018-12-10');

Expected result :

ID  Type    Date Created    Name
1   Client  2018-11-25      ff
2   Client  2018-10-25      cc
3   Client  2018-11-17      ff
4   Client  2018-09-25      aa
5   Client  2018-11-10      cc
6   Client  2018-12-05      ff
7   Client  2018-11-02      cc
8   Server  2018-11-19      tt
9   Server  2018-12-10      tt

So basically I want for each ID from Table 2 the name from Table 1 based on the rule : If the type is the same , I want to return the name when the difference between Date Created and Date is min .

For example, for ID 6 , the query should return the name ff because the min difference between 05.12.2018 and any date from Table 1 is 18 . ( Date 17.11.2018 )

Date Created >= Date as a rule .

I've tried to create a SQL query but without results .

Can you please help me with this ?


texts: Is there a value that I can use in a SELECT TOP that will return all rows?

texts:
I'm allowing the end user to define how many rows are returned by a query (SELECT TOP (@x)). Is there a value that can be entered where all rows are returned? Or do I have to dynamically create the query without the TOP (@x) if they want all rows returned?

I'm using SQL Server 2012.


texts: How to setup a script that automatically clones the DB every 2 hours?

texts:
I'm planning to build a system that will have 30+ tables and 100+ million rows in a few of those. Going to use MySQL - InnoDB (any better alternative for this?)

My scripts are going to add a couple of hundreds of thousands of clicks to the database every day. On the other hand, I'd like to do heavy database queries during the day as well.

What I came us with is to have two different servers. Server A would take all the clicks and store them and Server B would work on retrieving the results.

Question A: Is this the right approach to do?
Question B: Is it possible to set up a script that's cloning the database over from Server A to Server B - so the data is semi-up to date?


texts:
Is there any way to get the timespan of the data contained in a TRN-File?

What I have:

A backup of a transaction-log

What I need:

The timestamp of the first and the last entry inside the log

How do I get this information?


texts:
Not really sure if this question belongs here, but I hope someone could help me out.

I've made integration tests going all the way down to the database (using mssql localDB). I want each test to run independently with it's own data - I want to reseed the database with my fake data before each test is running. I tried to implement it with transactions without success. Here is how I tried to pull it off:

public class TestDbInitializer : DropCreateAlways<MyContext>()
{
    public static List<Item> Items;

    public override Seed(DbContext context)
    {
        Items = new List<Item>();

        // Adding items
        // ..

        Items.ForEach(x => context.Add(x));

        context.SaveChanges();
    }
}

public class BaseTransactionsTests
{
    private TransactionScope _scope

    [TestInitialize]
    public void Initialize()
    {
        _scope = new TransactionScope();
    }

    [TestCleanup]
    public void Cleanup()
    {
        _scope.Dispose();
    }
}

[TestClass]
public class IntegrationTests : BaseTransactionsTests

private IDependenciesContainer _container;

public static void AssemblyInit(TestContext context)
{
    Database.SetInitializer(new TestDbInitializer());

    _container = new DependenciesContainer();

    // Registers all my application's dependencies
    _container.RegisterAll();
}

[TestInitialize]
public void Initialize()
{
    using (var context = new MyContext("TestsDatabase"))
    {
        context.Initialize(true);
    }
}

[TestMethod]
public void TestAddItem()
{
    var controller = _container.Resolve<MyController>();

    var result = controller.AddItem(new Item({Name = "Test"}))

    var goodResult = result as OkNegotiatedResult<string>();

    if (result == null)
        Assert.Fail("Bad result")

    using (var context = new MyContext("TestsDatabase"))
    {
        Assert.AreEqual(context.Items.Count, TestDbInitializer.Items.Count + 1)
    }
}

I use my dependency injector in my tests, registering all dependencies once (AssemblyInitialize).

I created a DB instance for testings, and a specific DropCreateAlways initializer with a fake data Seed method, which I set as the initializer in the AssemblyInitialize as well.

I want to reseed the database with my fake data before each test run. For that case I implemented the base class which holds a transaction scope.

When I run my tests, the following exception is thrown when Seeding the database in the TestInitialize:

DROP DATABASE statement cannot be used inside a user transaction

How should I deal with it? Moreover, what do you think of my implementation of those integration tests? What could be improved?


texts:
I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.

In my table I have 3 columns

CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, url NVARCHAR(255))

and I am running this simple query.

SELECT url
FROM MyTable
WHERE ipto <= somevalue AND ipfrom >= somevalue

I have also created indexes on all 3 columns clustered on ipFrom and non-clustered on rest of 2. But this query giving me really poor performance in terms of CPU & reads.

Any suggestions.

What i have implemented is i like to redirect user based on the IP address.
I have stored several IP ranges from different areas and states and redirect users on the basis of their IP to appropriate URL.

Yes i think i inserted the data in wrong manner in both columns(and will do column rename later), but point here is to minimize the CPU.

When i look into the execution plan there it converts data in where clause, i dont know why it is converting data in where clause. There is something like this

|--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > Convert([@V])


texts: cross database ownership - The server principal is not able to access the database under the current security context

texts:
I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on,
       SQL='USE ' + QUOTENAME(name) + char(10) + 'EXEC sp_changedbowner ''MY_COMPANY\sqldbslaveowner'';'  + CHAR(10),
       CROSS_DB_OWNERSHIP_SQL='USE MASTER' + CHAR(10) + 'ALTER DATABASE ' + QUOTENAME(name) + SPACE(1) + 'SET DB_CHAINING ON;' + CHAR(10)
from sys.databases
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

It sets the db owner to a login, that has no permissions associated with it - my_company\sqldbslaveowner. It is an AD account.
and the following:

USE MASTER ALTER DATABASE [JUNOCORE] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [JUNOFinance] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [apcore] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [JUNOCore_Repl_Pub] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [apia_repl_sub] SET DB_CHAINING ON;

so, after done that, when I run the below I am sorted:

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on
from sys.databases
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

This was all good, and working fine, then yesterday, for no apparent reason, it broke up, and  I can't figure it out the bit that is missing.

What I am experiencing is the following (it is all within the databases shown above):

in one of the databases junofinance I go ahead and create a stored procedure that selects data from a table in another database called JunoCore. It should all be good since we are under cross database ownership.

Note that the procedure has execute as owner and the owner is the same guy on both databases.

I grant permission to execute the procedure to one of my application logins.

use JUNOFinance
go
create proc prc2
with execute as owner
as
begin
select * from junocore.[dbo].[agency]
end
go
grant execute on prc2 to [juno]
go

Now when I execute this procedure I get the following error:

use JUNOFinance
go
execute as login='juno'

exec prc2

  The server principal "My_Company\sqldbslaveowner" is not able to
  access the database "JUNOCORE" under the current security context.

This was working fine.

I will have to check on the  creating certificates as it was all working before!

The databases are part of an availability group

Only msdb is set to trustworthy

As I said it was all working, the only thing I have done lately is to upgrade sql server 2016 to SP2, but I did not have any issues with this server in particular.

what else can be suggested that I do to fix this scenario?


texts: Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

texts:
I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is there a way to apply a unique constraint to these 3 columns such that it only allows one unique, active user email for a specific company but any number of duplicate entires for inactive emails?

E.g.: Consider a 'Users' table with the following entries

CREATE TABLE users(
  user_id BIGINT(10) PRIMARY KEY AUTO_INCREMENT,
  user_email VARCHAR(255) NOT NULL,
  user_companyid BIGINT(10) NOT NULL,
  user_status enum('1', '0'))

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (3,'test1@gmail.com','777','1');

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 1
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1

I shouldn't be able to add an existing, active email for a specfic company twice; the following should fail:

INSERT INTO users(user_id, user_email, user_companyid, user_status)
 VALUES (4,'test1@gmail.com','555','1');

If I update the status of one of the active users to '0' (inactive), I should be able to insert the same email again since the previous email status is inactive. The following should succeed:

UPDATE users SET user_status = '0' WHERE user_id = 1;

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (4,'test1@gmail.com','555','1');

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 0
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1
      4 | test1@gmail.com |            555 | 1

Also, the constraint should allow duplicate entries for inactive user emails. This should also succeed:

UPDATE users SET user_status = '0' WHERE user_id = 4;

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | test1@gmail.com |            555 | 0
      2 | test2@gmail.com |            555 | 1
      3 | test1@gmail.com |            777 | 1
      4 | test1@gmail.com |            555 | 0


texts: Does the size of the primary key contribute to table size?

texts:
I have a table (InnoDB) with data length around 36G and index length 23G.

It had a composite primary key across three columns.

As an initial attempt to reduce the size of this table, I dropped the primary key (wasn't actually needed anyway). Yet, the data length and index length are the same.

Is this to be expected?

UPDATE
innodb_file_per_table is enabled on this database

UPDATE

mysql> show create table event;

CREATE TABLE `event` (
`owner_id` varchar(36) NOT NULL,
`key_id` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
KEY `owner_id_idx` (`owner_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Restored database copy larger than original

texts:
I've created a copy of a database for testing. It's on the same server as the original. However, tables are listed as being larger on the restored copy than on the original.

My original database is 148 MB, the copy is 232 MB. Table 'message' is 15 MB on the original, 28 MB on the copy. (I'm using queries like https://stackoverflow.com/a/1733523/492455 to get these values).

The CREATE TABLE statements (SHOW CREATE TABLE) are identical on both databases (except for the AUTO_INCREMENT value). Here's one for a table (33 MB original size, 76 MB copied size:

CREATE TABLE `mdl_log` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `time` bigint(10) NOT NULL DEFAULT '0',
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `ip` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `course` bigint(10) NOT NULL DEFAULT '0',
  `module` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cmid` bigint(10) NOT NULL DEFAULT '0',
  `action` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `url` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `info` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mdl_log_coumodact_ix` (`course`,`module`,`action`),
  KEY `mdl_log_tim_ix` (`time`),
  KEY `mdl_log_act_ix` (`action`),
  KEY `mdl_log_usecou_ix` (`userid`,`course`),
  KEY `mdl_log_cmi_ix` (`cmid`)
) ENGINE=InnoDB AUTO_INCREMENT=231557 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='Every action is logged as far as possible'

Can anyone suggest what's going on here?

MySQL Server v5.6.24 on CentOS 6.2.

Thanks,

Leon


texts: Having trouble with insert query on linked server

texts:
Two servers. Server A and Server B both has database named KPS. KPS has two tables complaint and attachment. KPS database on server A gets the value from the Web.

Both table if the submitted value = 1 then run the insert query which will insert the col1,col2 and col3 from Server A KPS to Server B KPS. They both are linked server. Both table attachment and complaint submit value needs to be 1 if it is 0 then do not insert. There will be two insert query one for complaint and one for attachment.

Can i also check that i m not inserting same data over and over ?

Thank you..


texts: Create Random Number up to 50 Digits and Store in Varchar

texts:
Does anyone have code; looking for a random number function generator.
I would just supply with a variable length @NumberLength = 50, etc.
It can create numbers up to 50 digits, and store in varchar. (Bigint does not store this high)
I am using Floor Rand, Floor, NewId(), still not receiving answer, that exceeds 12 digits.

If anyone has a solution, that would be great. I need to randomize a column in table.

Thank you,

Note: Needs to be in a function, functions cannot accept NewID or rand() for some reason, so I sometimes utilize views.
If random number is used again, no issue, we can have little bit repetition.


texts: Laggy slave OK to have only one system user in processlist?

texts:
In all the examples of a processlist on a mysql slave I've seen, there are two system users.  I have a slave that is very far behind the master.  When I do show a processlist, there is only one system user (State: "Waiting for master to send event"), the event scheduler, and a trigger user.  Is this a symptom, cause, or nothing to do with excessive lag?

Both servers are 5.6.19 on FreeBSD.  Topology: master --> slave --> slave in question.  Slave IO and SQL threads show as running.  Relay_Log_Space is very large.

show slave status\G
*************************** 1. row ***************************
           Slave_IO_State: Queueing master event to the relay log
              Master_Host: 10.02.00.0
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.004634
      Read_Master_Log_Pos: 473351239
           Relay_Log_File: slave-relay-bin.004460
            Relay_Log_Pos: 506886175
    Relay_Master_Log_File: master-bin.004515
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 506886012
          Relay_Log_Space: 62863753813
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 245653
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 42
              Master_UUID: a36f5f4b-0904-11e4-a130-b8ca3a622758
         Master_Info_File: /var/lib/mysql/master.info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: closing tables
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set:
            Auto_Position: 0
1 row in set (0.01 sec)

show processlist\G
*************************** 1. row ***************************
 Id: 1
User: event_scheduler
Host: localhost
 db: NULL
Command: Daemon
Time: 74
State: Waiting for next activation
Info: NULL
*************************** 2. row ***************************
 Id: 87720
User: system user
Host:
db: NULL
Command: Connect
Time: 8650
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
 Id: 87721
User: routine_user
Host: localhost
 db: db_name
Command: Connect
Time: 246665
State: closing tables
Info: SELECT
    IFNULL(CAST(LEFT(CONCAT('0', charValue), 10) as decimal(18,9)), 0)
INTO
    converted_


texts: Why are views not copied over when using SQL Server Log Shipping?

texts: I have 2 Sql Servers (2008) with Log Shipping setup between them. The receiving server is configured to keep the database in Read-Only mode. When creating a view on the primary server, this view is not copied over to the secondary. It was my believe that any DDL statements are also shipped. Can anyone clarify?


texts: Mysql Select recent 2 records for each group[user]

texts:
I have two tables users and login_track. Each user may or may not have login records in login_track table. I want to retrieve recent 2 login dates for each user.

Table structures as below:
users:

user_id    name
     1     John
     2     Mike
     3     Anderson
    .....

login_track:

  track_id   user_id   login_date
   100           1    2017-06-20
   101           1    2017-06-21
   102           2    2017-06-21
   103           1    2017-06-23
   104           2    2017-06-23
   105           1    2017-06-27

So desired result should be.

 user_id  name         login_date
    1      John        2017-06-27, 2017-06-23
    2      Mike        2017-06-23, 2017-06-21
    3      Anderson    NULL

Note : User id 3 (Anderson) has no records in login_track table, even though it should list in the final result with NULL value as login_date column.


texts:
I have a question about XTP_CHECKPOINT.

I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated.

There are no open transactions. I've run DBCC OPENTRAN and it returns:

  "No active open transactions."

But I keep getting this message whenever I try to create or drop a table or delete data:
(I've replaced my actual database name with the word database_name)

  "The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'"

Does anyone know why this might be happening, and, more importantly, how can I make it stop?

And yes, the database really is in SIMPLE recovery model mode. i.e. The transaction log should truncate automatically.

Incidentally, another database that I have in full recovery mode did the same thing, started returning the same error:

  The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'

I tried to change the log growth settings to unlimited growth, but it wouldn't let me, returning the same error.

I can reproduce the problem without any XTP stuff at all, except for just the filegroup. Here's how: http://pastebin.com/jWSiEU9U


texts:
In an effort to more easily migrate from Visual Basic to plpgsql I'd like to create a string as a domain in Postgres. Is there any inherent risk in doing so?

CREATE domain string AS TEXT;

I ask because of some c process or perhaps string is reserved?


texts: In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?

texts:
I have a SQL Server with numeric types for few columns.

When getting a table structure, I have the following results:

Query:

    SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE'
  AND DATA_TYPE = 'numeric'

The result is the following (truncated):

CHARACTER_MAXIMUM_LENGTH  NUMERIC_PRECISION   NUMERIC_SCALE
NULL                     2                   0

Will the value CHARACTER_MAXIMUM_LENGTH always be NULL for a numeric ? Don't scale and precision impact the character length of the value ?


texts: How to change the owner of a Stored Procedure in DB2?

texts:
Let's say that I have a procedure called schema.proc1.
How do I change the owner of it? I'm using db2 express-c 10.5

I've tried the following command running as the olduser,

transfer ownership of procedure schema.proc1 to user newuser preserve privileges;

I want to do this because I believe this is a constraint that I'm having to not being able to create or replace schema.proc1 as newuser.

I would like to point out what I've done regarding privileges and the newUser (all of this were done with the oldUser).

TRANSFER OWNERSHIP OF SCHEMA mySchema TO USER newUser PRESERVE PRIVILEGES;
GRANT ALTERIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT CREATEIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT DROPIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;

GRANT BINDADD ON DATABASE TO USER newUser with grant option;
GRANT CONNECT ON DATABASE TO USER newUser with grant option;
GRANT CREATETAB ON DATABASE TO USER newUser with grant option;
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER newUser with grant option;
GRANT LOAD ON DATABASE TO USER newUser with grant option;
GRANT QUIESCE_CONNECT ON DATABASE TO USER newUser with grant option;
GRANT EXPLAIN ON DATABASE TO USER newUser with grant option;
GRANT SQLADM ON DATABASE TO USER newUser with grant option;
GRANT WLMADM ON DATABASE TO USER newUser with grant option;

grant createin on schema mySchema to newUser with grant option;
grant alterin on schema mySchema to newUser with grant option;
grant dropin on schema mySchema to newUser with grant option;

Event with all of this, still no luck on replacing a stored procedure.

Thanks in advance!


texts: Mariadb / mysql table partitionning column usage

texts:
I read mariadb documention :https://mariadb.com/kb/en/library/partition-maintenance/

And it sayed :

  The partition field should not be the field first in any key

But in mysql documentation :https://dev.mysql.com/doc/mysql-partitioning-excerpt/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html

And exemples use the first field in key (unique key) for partitioning.

CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

So what is the trouble if the partition field is the first field of a key ?


texts: Downgrading Enterprise to Standard Edition

texts:
I'm being asked to downgrade some non critical database environments from Enterprise Edition 2012 to Standard Edition 2012. Due to ongoing licensing costs.

Can I do this? What are the drawbacks?

Is there a process similar to upgrading I can do? Can I just run the standard edition install as an upgrade?

We currently don't have development environment for this databases.


texts: Moving SQL server 2016 DB's from mirroring to HA

texts:
We have Windows Server 2016 machines with SQL server 2016 standard edition. Currently mirroring is implemented and now are planning to move to HA with 1 listener for each DB. We have multiple DB's

In few DB stored procedure we have call to other DB with hardcode name.

Once we move to HA with 1 listener for each DB, will this new configuration be any issue for me?


texts: How to determine the best index on a table with 3 nullable columns?

texts:
I am examining a users table similar to this:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(81) DEFAULT NULL,
  `mobile_phone_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

I am not sure if I need a big index comprising the 3 primary searched and identifiers : username,email,mobile_phone_number .
I need all of these fields to be searched as quick as possible.
Main queries contain where conditions on either email and mobile_phone_number or username. As well as each on their own.

How can I determine the best index over these ?

My fear of using a index over all 3 is that it would not be efficient in some of my main queries as they are all nullable columns. Is the column beeing nullable even relevant to the index use ?


texts: Availability Group database stuck in Initializing / In Recovery mode even after restarting service

texts:
While setting up a new replica in an Availability Group on our reporting cluster a few of the databases were not seeded correctly. I tried manually seeding them using a recent backup and applying the latest log file backups, which worked for most of the databases but a few of them are stuck in Initializing / In Recovery on startup in SSMS. Even restarting the SQL Server service didn't help. Here are the steps I have tried and the resulting errors:

--Get a list of database not Online and not Restoring
select name,state,state_desc from sys.databases where state not in (0,1)

name                        state   state_desc
StackExchange.Aviation.Meta 2       RECOVERING

drop database [StackExchange.Aviation.Meta]
Msg 3702, Level 16, State 4, Line 21
Cannot drop database "StackExchange.Aviation.Meta" because it is currently in use.

RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\StackExchange.Aviation.Meta_FULL_COPY_ONLY_20170901_040130.bak' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 12
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] with recovery
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

ALTER DATABASE [StackExchange.Aviation.Meta] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Msg 5061, Level 16, State 1, Line 5
ALTER DATABASE failed because a lock could not be placed on database 'StackExchange.Aviation.Meta'. Try again later.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

Even stopping and restarting the sql service did not help, as it comes back in the same stuck state. What else can I do to re-seed this database?


texts: Partial rollback doesn't decrement trancount

texts:
Suppose I have an open SQL Server session, and do the following:

begin tran

insert into People (Id) values (1)

select @@TRANCOUNT -- Prints 1

save transaction tt

begin tran

select @@TRANCOUNT -- Prints 2

insert into People (Id) values (2)

Ok, so now I make a:

rollback tran tt

select @@TRANCOUNT -- Prints 2!

My question is maybe obvious:

Why partial rollback doesn't decrement @@TRANCOUNT increased by the inner transaction?

To commit changes I should do two commit commands. IMHO doesn't seems natural.


texts:
I have two MySQL (MariaDB actually) servers, a master and a slave with standard replication.
Two times already in less than a month I've had my slave become unusable.

This is what I experienced:

Notice a drop in query per second on the slave with my monitoring tool
Connect to the server with the mysql client, run SHOW SLAVE STATUS \G
Notice everything looks normal (Waiting for master to send event, seconds behind master = 0, etc)
Try and stop the replication with STOP SLAVE
STOP SLAVE hangs
Impossible to connect to the server with a client
Execute SHOW SLAVE STATUS \G on another already connected client, and it hangs too.

After that, I saw the MySQL process using CPU, and I saw a lot of disk writes with iotop, so I let it do its stuff for a while.

After 15 minutes I killed -9 the MySQL process because I got frustrated. I then restart MySQL, and I can connect and run queries, good.

I execute SHOW SLAVE STATUS \G which now works, and it looks normal: seconds behind master = 0, waiting for events. Then I go on the master and execute CREATE DATABASE foobar to see if the replication works, and it doesn't.

Again, at that point I see a lot of disk writes so I assume the replication is catching up.
Two hours later, I finally decide to check SHOW PROCESSLIST and all I see being executed is a OPTIMIZE TABLE table1 ... and then I remember that I had that exact same problem last time.

I kill the process with that query, then I reexecute SHOW PROCESSLIST and sure enough it's catching up with the master. SHOW SLAVE STAUTS \G reported 15k seconds behind master, which was coherent.

Anyway, after that wall of text, my questions:

why does a OPTIMIZE query block the replication catching up ?
can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Thanks.


texts: DELETE command not completing on 30,000,000 row table

texts:
I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I converted the table from MyISAM to InnoDB and am looking to delete a lot of the rows that contain junk data.

The database is MySQL 5.0 and I have root access to the server. I was first running these commands through Adminer and then phpMyAdmin, both with the same results.

The command I am running is,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

Essentially, delete anything in this column that begins with a dash -.

It runs for about 3-5 minutes and then when I view the process list, it's gone.

I then run,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

and it returns millions of rows.

Why is my delete statement not completing?

PS, I am aware of how out-of-date MySQL 5.0 is. I am working on moving the DB to MySQL 5.6 w InnoDB (maybe MariaDB 10 w XtraDB) but until that happens, I am looking to answer this with the DB as is.

--

Edit removed, see my answer.


texts: Selecting Only One Record with Most Recent Date Using Table Join

texts:
I have a list of 463 CARRIER_CD.  I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD.

I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the table name is jjenkins_carriers.

The BILL_DT is in a database named edw_ar_fl and table named ARTBASE.  I wrote the query below in hopes of returning a single row of data including CARRIER_CD, BILL_DT, and BILL_AMT for the most recent BILL_DT associated with each of the unique CARRIER_CD.  However, the query returns 408+ million rows.  Any help would be appreciated.

SELECT edw_ar_fl.ARTBASE.CARRIER_CD,
    edw_ar_fl.ARTBASE.BILL_DT,
    edw_ar_fl.ARTBASE.BILL_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_carriers
         ON edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD
AND edw_ar_fl.ARTBASE.BILL_DT =
     (select max(edw_ar_fl.ARTBASE.BILL_DT)
      where edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD)


texts: Where does mysql write temporary tables?

texts:
MySQL 5.1.x | Windows Server 2008

Data Directory is on a different partition.

I have an innodb database (innodb_file_per_table is set) that I'm trying to optimize and I don't appear to have enough space.  When I run the optimize command, I see:

foodb.foo_table
note      : Table does not support optimize, doing recreate + analyze instead
error     : The table '#sql-123_foo' is full
status    : Operation failed

My understanding is that when I run OPTIMIZE TABLE, it's essentially running ALTER TABLE behind the scenes and that usually needs to make a copy of the table.  My question is, where is the table being created?


texts: How to join 2 tables in mysql but with different values in one of the tables

texts:
I have 2 tables(as shown by the image below) inbox_messages_table and users_table

So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?

my query so far is this :

// $user_id is the logged in user

SELECT * FROM inbox_messages_table i
    JOIN users_table u ON i.from_user_id = u.user_id
    WHERE u.banned = 0
        AND to_user_id = $user_id
        AND deleted = 0 ORDER BY message_timestamp DESC


texts:
In the WHERE clause of a SQL query I would expect these two conditions to have the same behavior:

NOT (a=1 AND b=1)

vs

a<>1 AND b<>1

The first condition behaves as expected, and while I epxect the second condition to do the same thing, it does not.

This is very basic stuff, but ashamedly I can't see what I'm doing wrong.


texts: What exactly is metadata? How is metadata different than regular data?

texts: As I understand, metadata is data about data- so I think of analytics; is my simple definition correct? How is metadata different than data?  I'm simply asking to broaden my understanding-thanks!


texts: Integrity constraints in a relational database - should we overlook them?

texts:
I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a relational database in order to speed up large queries and to gain better performance.
The platform under consideration is MySQL 5.x, and no FOREIGN KEY has been set up, even some PRIMARY KEY constraints of the relevant tables are missing which, at least for me, is not reasonable. Maybe they’re right and I’m wrong, but I don’t have enough arguments to discuss about this situation.
This has been the preferred approach for three years now. I’m new in this company (only one month) but, as the product “works”, there is hesitation to enhance the database; nevertheles, the first thing I noticed is one page taking 1 minute to load (yes, 60 seconds!).
One of the claims behind the current state of affairs is that a “denormalized” database is faster than a normalized one, but I don’t believe that’s true.
Most of the relevant queries include JOIN operations, which makes them run very, very, very slow with large amounts of data (the database contains millions of rows).
Commonly, the handling of “CRUD” operations is implemented at the application program code level; for example, in order to DELETE some data FROM, let’s say, TableA:

it is necessary to first check on the fly if there is some relationship between the rows of TableA and TableB,
in case that said relationship is “detected”, then the app program code won’t allow to DELETE the pertinent row(s), but
if for some reason the app program code fails, then the DELETE operation will “succeed”, no matter if there is any relationship regarding the involved rows and tables.

Question
Could you help me to elaborate a good, accurate and solid answer to enrich the debate?

Note: Maybe something like this has been asked (and answered) before, but I couldn’t find anything by means of Google.


texts: Is Non-Clustered index with all others columns included equivalent to a Clustered Index

texts:
Is a nonclustered index on a column with all other columns included almost like a clustered index on a different column? Will it actually duplicate the full Table?

Normally you might not need to do that because looking up the rows might be less costly than duplicating data but I am asking from a theoretical perspective.

I use the following database systems MSSQL,PostgreSQL and Oracle. Would it depend on different databases?


texts:
I have a SQL Server 2014 Enterprise Edition where the SSISDB catalog is already here, but corrupted because of a rogue package that made it balloon to enormous size and a failed cleanup attempt.

Is there any way to create the SSISDB catalog anew?


texts: Checking SQL Server's memory utilization

texts:
Is there a way to check how much of the memory reserved by SQL Server is actually being used by SQL Server?

Is there a way to look at this information in the past? I.e. how much memory was being used an hour ago?


texts: postgresql cannot connect as user to data server

texts:
I'm very new to postgresql, I installed it on my windows machine, and created a few databases and users, and I'm trying to connect with one of the accounts I created. If I log in using the postgres account, and I list all users, I get the following:

    postgres=# \du
                                         List of roles
   Role name    |                         Attributes                         |    Member of
----------------+------------------------------------------------------------+------------------
 oak_admin      | Superuser, Create DB                                       | {}
 oak_admin_r    | Cannot login                                               | {}
 oak_test_roles | Password valid until infinity                              | {}
 oak_test_user  | Password valid until infinity                              | {oak_test_roles}
 oak_user       |                                                            | {}
 oak_user_roles | Password valid until infinity                              | {}
 postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

now I listed all the databases, and I got the following:

postgres=# \l
                                                  List of databases
   Name    |  Owner   | Encoding |           Collate           |            Ctype            |   Access privileges
-----------+----------+----------+-----------------------------+-----------------------------+-----------------------
 oakdb     | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
 postgres  | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
 template0 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
(5 rows)

With that I thought I had the users and I had the databases, so I should be able to connect. So I thought maybe I mistyped the password, so I altered the password to something very easy and tried that out, I got the following error:

postgres=# ALTER USER oak_admin WITH PASSWORD  'very_easy';
ALTER ROLE
postgres=# \q

C:\Users\charb>psql -U oak_admin
Password for user oak_admin:
psql: FATAL:  database "oak_admin" does not exist

Any ideas what am I doing wrong?


texts: A Query with a Dynamic Case clause or how to avoid Case When

texts:
Let's take an everyday query with a CASE clause so I can show you the problem, see how we'd have to change the query when a new Certification comes out:
select count(*) as "certCount",
CASE
when c.id = 1 then 'AWS Cloud Practitioner'
when c.id = 2 then 'AWS Alexa Skill Builder'
when c.id = 3 then 'AWS Solution Architect Associate'
when c.id = 4 then 'AWS Developer Associate'
when c.id = 5 then 'AWS SysOps Associate'
when c.id = 6 then 'AWS Solution Architect Professional'
when c.id = 7 then 'AWS DevOps Professional'
when c.id = 8 then 'AWS Security'
when c.id = 9 then 'AWS Networking'
when c.id = 10 then 'AWS Big Data'
when c.id = 11 then 'AWS Machine Learning'
ELSE 'N/A'
END AS name
from certification c
inner join qualification q on c.id = q.certificationid
group by q.certificationid, c.id

Sure we could generate the SQL and execute it in an Ad-Hoc fashion. This is what I've done in the past, though I'm wondering is there another way, like some way of joining that avoids the CASE WHENs?

Schema and sample data below, its in PostGres but I'd also be interested in Dynamic Case statements for Oracle, SQLServer, etc:
 CREATE TABLE certification (
    id serial NOT NULL,
    officialcertname text NOT NULL,
    "name" text NOT NULL,
    vendorid int4 NOT NULL DEFAULT 1,
    isdeleted bool NOT NULL DEFAULT false,
    CONSTRAINT certification_pkey PRIMARY KEY (id)
);

CREATE TABLE qualification (
    id serial NOT NULL,
    employeeid int4 NOT NULL,
    certificationid int4 NOT NULL,
    date_attained timestamptz NULL,
    date_expiry timestamptz NULL,
    certurl text NULL,
    verified bool NOT NULL DEFAULT false,
    created_by text NOT NULL,
    created_date timestamptz NOT NULL,
    modified_by text NULL,
    modified_date timestamptz NULL,
    CONSTRAINT qualification_pkey PRIMARY KEY (id)
);

INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES
('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false)
,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false)
,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false)
,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false)
,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false)
,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false)
,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false)
,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false)
,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false)
,('AWS Certified Big Data - Specialty','AWS Big Data',1,false)
;


texts: How to find who changed a table value in SQL Server 2014?

texts:
We have a few tables on a database called mydb which is being used by applications to store, or retrieve records. Somehow, someone changed a value on the test table and that caused a serious issue.

We are receiving constant data from different sources, when the data is being updated on the table, a trigger fires and checks for the specific value, if the values is 360 then it will update the rows, else it will do nothing.

Now I have two questions:

I want to know who changed that value 4 days ago?
I dont see any successful login (user login) in the SQL server error log. I assume that someone has used a generic user account named user. Now who used that account to login?

The value on the table is supposed to be 350, but it was changed to 800.

Any tips?


texts: Database Recovery Model - Simple vs. Full

texts:
Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model? i.e. keep record of each insert/update/delete transaction?

If this is the case, then why should FULL Recovery model be used? In SIMPLE Recovery, Checkpoints run every minute on a busy database, so the data is written to disk.

Where does the data loss potentially occur on SIMPLE Recovery Model?

Thanks!


texts:
My SQL Server has a weird problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used dbcc memorystatus to check the problem. I found MEMORYCLERK_SQLOPTIMIZER singlepage allocator ocuuping a lot of memory. I guess it could be why my SQL server restarted.

So I used sp_WhoIsActive to find which query script holds memory. After I collect some data, I don't know how to distinguish the relationship between sp_WhoIsActive result and  dbcc memorystatus.

Could you help me and give me some hints to solve the problem?

The following is some information about my SQL Server:

Server: CPU: intel E5645*2, RAM 64GB, HD: 10krpm SAS
SQL Server: 2008r2 with failover cluster feature enabled

max memory:52GB,
instance:1
No of db: 1700(Don't be surprised. There are 1700 dbs in my SQL Server.)

The attach file can be downloaded here.

After unzipping, there are 3 files.

att1.txt - The status before server is going to restart.
att2.txt - The status that the server has run for two weeks after restarting.
whoisactive.bak - The result for sp_WhoIsActive.

You can restore it in the SQL server2012.

My SQL Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Finally I turned on optimize for ad hoc workloads, but it didn't work. I am not sure what happened. Now I restart service when optimizer memory almost holds up all the memory. And all the memory is released.


texts: Any script to auto test tune and benchmark mysql config?

texts:
I'm looking or willing to do a script to benchmark mysql config variations for a specific system.

A script that would change certain mysql config values and do a sysbench, save results, change config and restart service, and test again.

max_connections = A
table_open_cache = B
query_cache_type = C
innodb_buffer_pool_size = D
innodb_buffer_pool_instances = E
innodb_adaptive_hash_index_partitions = F

...

And the script does a sysbench looping through all possibilities that make sense:

on A takes values of 50 100 200 250
on B takes values of ....

I know it depends on system, but it would be great to have a recommended "set" of values / ranges to test. (Even check the current free RAM and do a small CPU bench and with this values create the set of values to test)

Is there anything outside that can acomplish this?

Would it be useful for you or it's not worth doing it?


texts: Multi-statement Table Valued Function causing massive PAGELATCH_EX waits

texts:
We are getting insane PAGELATCH_EX waits because of a Multi-Statement TVF. I've found that the temp table created by the function is on tempdb file #1 and doesn’t ever change (always '#A465FC0D'). It appears to be permanently cached there. The function is very heavily used and all the processes line up waiting to access the same temp table.

The function simply does a string_split, but using string_split directly, or converting the function to an inline TVF is 7x slower. Strings to split are relatively small (max 200 ids, but many are just a few ids)

CREATE FUNCTION [dbo].[IntListToTable] (@list nvarchar(max))
RETURNS @intlist TABLE (val int)
AS
BEGIN
  insert into @intlist
  SELECT
   CAST(value as int) val
  FROM
   string_split(@list, ',')
  return
END

I understand that the page latches are caused by the use of this function, but I'm looking to understand the caching/re-use mechanism here better.

What about this function makes it forever re-usable?
And why is this better than using an inline TVF or STRING_SPLIT() directly?

Other helpful information:

SQL Server 2017 CU2
We have 24 temp db data files
This happens on a couple of dedicated read replicas that have a more narrow workload than our OLTP server. Both have buffer cache hit ratio of 100% and plan cache hit ratio of 88%.
We restarted the instance and a new temp table was created and its been that same one ever since.


texts: Sargability question - order of variables

texts:
I have a stored procedure that has a few lines:

AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate )
AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate )

Should it be re-written as:

AND ( r.ReferralDate >= @StartDate or @Startdate IS NULL  )
AND ( r.ReferralDate <= @EndDate or @EndDate IS NULL )

I tried it both ways and looking at the execution plans. There is a small difference in estimated number of rows, but other than that I do not see a change, so I assumed that the order in the statement did not matter, but was hoping someone could verify.


texts:
I utilize the resource governor on my 2016 Sql Server.  In prior 2016 sql server  versions, this query:

select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'

Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load.  However, now they are always zero.  I also used perfmon to confirm this (along with running legit queries).  Does anyone know why or if this counter changed?  According to the msdn article, it should still work.


texts: How to copy database from server to local machine in SQL Server Management studio?

texts: I am pretty much new to SQL Server Management Studio. I connect to SQL Server using Windows authentication. But not sure if my work affect the server database. How can I make sure not to mess with the updates and commits to the data?


texts:
I Want to undo DDL changes on View, how to do that?

I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If there is any chance to use logminer without setting this parameter(without restarting database) ?

Waiting you suggestions, thank you


texts: How do I host and separate multiple projects on one Oracle Database?

texts:
From Difference between database vs user vs schema

I'm trying to understand the concepts of the schema, user and database.

My current understanding is you cannot create a schema separately.
You can create a user and a schema will be automatically created.

Then my question is, what if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

For example, in a project called "Killer Web", I have 3 application developers and I am the DBA.

So I created 3 users for each developer, dev1, dev2 and dev3.
And I granted on these three devs, access and other privileges to the actual schema on which the 3 devs will actually work.
Would that create 3 schemata, empty and unused?

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

How do I host multiple projects on one Oracle database and separate them?

In MySQL, I would just create a database for each project. (Not sure if this is the correct practice though.)


texts: In a database there is a username called u1, someone tried to access u1 but due to wrong password it got locked. How to find who did this suspicious activity.


texts: Help with stored procedure and update table

texts:
I am trying to write a stored procedure that has parameters and updates a table with the supplied title and middleName for the person having the businessEnityID.

Create Procedure sp_PersonEditor
 @BusinessEntityId (int, not null),
 @PersonType (nchar(2), not null),
 @NameStyle (bit, not null),
 @Title (nvarchar(8),  null),
 @FirstName (nvarchar (50), not null),
 @MiddleName (nvarchar (50), null),
 @LastName (nvarchar (50), not null),
 @Suffix (nvarchar (10), null),
 @ModifiedDate (datetime, not null)
as
 update BussinessEntityId
 set Title = @NewTitle, MiddleName = @NewMiddleName
 where BussinessEntityId = @BussinessEntityId
end

This is the error message I am getting:

  Incorrect syntax near ','.

These are my columns for the database.

BusinessEntityId (int, not null)
PersonType (nchar(2), not null)
NameStyle (bit, not null)
Title (nvarchar(8),  null)
FirstName (nvarchar(50), not null)
MiddleName (nvarchar(50), null)
LastName (nvarchar(50),not null)
Suffix (nvarchar(10,)null)
ModifiedDate (datetime, not null)


texts: Mongodb - see when a database was dropped

texts:
I want to know if there is a way to see when a database was dropped.
Is there an entry in a log file or some other way to see when this occurred


texts: Local Backup from Amazon RDS PostgreSQL database

texts:
I have a PostgreSQL database on an Amazon RDS instance.

I want to have a local dump of this instance.

So far, I've tried with this:

PGPASSWORD="Password" pg_dump -h mydb.xxxxxx.us-east-1.rds.amazonaws.com -p 5432

But it keeps throwing an error, saying that the server refuse the connection, and check if the server is actually running on port 5432, which is actually doing.

All my credentials and DB path/codes are correct.


texts: How can I automatically trigger a script when my DB fails over?

texts: I have some Windows services that I'd like to restart after a database failover but I can't find any details on triggering external actions after a failover. I'm working with SQL Server 2012 database mirroring.


texts: Transforming Huge Database table to Another Table

texts:
We are migrating lot of data from our old database to new database.

Presently, both of them are actively[writing/reading] used based on flows/activity as migration is in progress.

Here's the real challenge for me now. We have old database table which's still getting used now.

  Atleast 1,00,000 new records are getting inserted every day and we
  need to move 6months old data [01-01-2014] to new database.

We are using MySQL in both places. The new table to old table need some logical-transformation of data which will be carried through Java-JDBC, as the table structures and functionality is changed.

What are the ideal steps that I need to follow and taken care to do this transformation as quick as possible without fail.

As per my calculation, we will be migrating atleast 2,00,00,000 records.

  Some info:

Presently index exists only on ID column. creation_date exists without index
How to fetch efficiently these records, a better query to fetch??
Efficient way to transform using multi-threading
Efficient way to write the data into new database


texts: How to optimize 'group by' query and indexing on big table?

texts:
My table has over 300+ million records. I created a key store_idx consisting of columns (store_region, store_format, store_code). This is my query:

select store_region,
       store_format,
       store_code,
       count(distinct basket_id),
       min(shop_date),
       max(shop_date)
from basket
group by store_region,
         store_format,
         store_code
order by store_region,
         store_format,
         store_code;

When I ran explain command this was the output:

+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows      | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+
|  1 | SIMPLE      | basket | NULL       | index | store_idx     | store_idx | 3069    | NULL | 305100051 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+

This query is taking so long to run. How can I optimize?


texts: How can I create a bool column for a view based on the result of a statement?

texts:
I'm using PostgreSQL as my database.

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  group     INT           NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (group)
    REFERENCES groups(id),
  UNIQUE (group),
  UNIQUE (name)
);

I want to create a view that includes a boolean column stating whether or not a group has any users based on the has many / belongs to relationship of the two tables above. If it's easier, I'd even be fine with an integer column stating how many users a group has in it. How can I do this in my view?

CREATE VIEW group_info AS
  SELECT DISTINCT
    g.name        AS name,
    ????          AS empty
  FROM groups AS g
  JOIN users AS u
    ON (g.id = u.group)
;

I've looked into using BOOL_OR and/or COUNT to help with testing the existence of a group ID in the users table, but I'm not seeing how to put it all together.


texts: Rebuilding fragmented indexes on SQL Server

texts:
We've got a SQL Server database powering our SAAS web app. It's a SQL Azure database, hosted on S3 Standard plan (100 DTU units - pretty suficient for what we need).

Our DB indexes fragment really quickly, to the point that after 3-4 days, a lot of our more used tables are usually over 40% fragmented. If left, after 2 weeks a lot would be ~90% fragemented. In our most used tables, we've got about 2 million rows.

So, to sort this, we've got a script that runs every 3 days, looking for indexes that are >20% fragmented, and we rebuild these (with online on).

The problem with that is while it's running, the app becomes very slow and unresponsive for about 2 hours while it does it's thing.

Is there a better strategy to deal with this fragmentation of indexes, or anything else I could look into to try and make the fragmentation less frequent?

Thanks.


texts: Is MongoDB Connections getting too high?

texts:
I currently have my MongoDB running in Production Environment. It has 1 Primary, 1 secondary and an Arbiter. Configuration of the ubuntu machines are pretty decent, it's got 56GB of RAM and about 100GB of HardDisk space. It's been around a week's time since MongoDb has been running continuously. I did some reading about the uLimit and it is set as

httpd soft nofile 10240
httpd hard nofile 10240
* soft nofile 65000
* hard nofile 65000

Our server-side is Java and we have implemented connection pooling. Today when I did a  db.serverStatus(), under connections I got the following

"connections" : {
            "current" : 19314,
            "available" : 32686,
            "totalCreated" : NumberLong(53770)
    },

Is this something which I should be concerned with, because I could not find any limit or number which tells me I've reached a danger limit before mongoDB crashes. Can anyone let me know if there is anything I should be doing?