texts:
I have two MySQL databases that are for Wordpress websites. One db is for a live version of a site, the second is a copy of the original that is used in conjunction with a copy of the original site. We use the copies for development.

The original site is having character encoding issues, showing the black diamonds with question marks in them. I've gone all over the web looking into this, but nothing is fixing anything. The strange thing is that the second database, which is a copy of the original, displays characters correctly. How could this be? Its nothing with my site files, as I've redirected the original site to use the copy of the database temporarily and the characters display correct on the live site.

Both databases have "latin_swedish_ci" as the database collation and the wp_posts table that fetches the content I'm having problems with is set to a "utf_8" charset with "utf8_general_ci" as the collation. I've tried setting the whole database collation to utf8 (general and unicode), as well as changing the specific database table collations to utf8_general, unicode and tried a few others. It seems like nothing changes at all, I even chose a few arbitrary charsets and the characters just appeared exactly the same. Could this be related to user permissions perhaps, if thats the case?

Thanks for the help in advance.


texts: can I use many-to-many relation with weak entity

texts:
I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees.

I have 3 options:

1) Create weak entity -  responsibility when the key is responsibility name and employee ID from employee entity.

And those entities will a have many-to-many relation  .

2) Make an employee-employee relation which will hold attribute responsibility.

3) Add attribute to employee as a key(ID, responsibility)

Is first option even possible!!! And what is the correct option.


texts: Capitalize only the first letter of a name

texts:
I have a table Vendors that contains a column VendorContactFname, which
is written in lowercase.

I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first letter to the left, with all other remaining letters to its right in their original lower case, i.e., with the substring:

Upper(Left(VendorContactFname,1) ) +
Substring('VendorContactFName',2, Len('contactFname')-1 )

Is there a better way to do this?


texts:
I have an oracle database:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

and I would like to run my script stored on db server.

path: /d01/oracle/scripts/super_skripta.sh

The content of my script (test script):

IME=$(date +"%s")

touch /d01/oracle/scripts/$IME

Can someone explain me (step by step) how can I run this script from a oracle database?

I tried to create an executable program like this:

begin
dbms_scheduler.create_program
(
program_name => 'SUPER_SKRIPTA',
program_type => 'EXECUTABLE',
program_action => '/d01/oracle/scripts/super_skripta.sh',
enabled => TRUE,
comments => 'super skripta'
);
end;
/

and then create a job:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"WOLFY"."JOB_SS"',
            program_name => '"WOLFY"."SUPER_SKRIPTA"',
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '',

            job_style => 'REGULAR');

    DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"WOLFY"."JOB_SS"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

    DBMS_SCHEDULER.enable(
             name => '"WOLFY"."JOB_SS"');
END;

but it does not work...


texts:
I have configured a MariaDB Galera cluster with three nodes. The master node and one slave-master node is up and running, but the third node just won't run. The system specs and cluster config are the same on all three nodes (except changing the wsrep_node_address and the wsrep_node_name on each node).

Here is the config for the nodes (wsrep_node_address and the wsrep_node_name are unique on each node):

[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.9.113'
wsrep_node_name='george-db4'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=username:password

It seems like MariaDb does not like the config

wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"

When I comment it out MariaDB restarts just fine. When I uncomment it, it just displays this message:

Starting MySQL................................... ERROR!
ERROR! Failed to restart server.

When I change the wsrep_cluster_address to just

wsrep_cluster_address="gcomm://

MariaDb runs with no problem. So I am not sure why it is having problems with assigning the IP address (even if it works fine on the other two nodes). I have tried emptying the galera.cache file, I have tried re-installing the cluster, I have tried rebooting the machine etc. and nothing works so far. I even truncated the log file /var/lib/mysql/db1.err with the command truncate logfile --size 0 (now I cannot figure out how to get my log file back with space as I realized I would need the log file).


texts: SQL Server silent installation does not work

texts:
We need to install SQL Server Express 2008 silently without any user interaction.
I am using this code but in the middle of the installation the user interface comes out
SQLEXPR_x86_ENU.exe /q
/INSTANCEID="SQLExpress"
/ACTION="Install"
/FEATURES=SQLENGINE,REPLICATION
/HELP="False"
/INDICATEPROGRESS="False"
/IACCEPTSQLSERVERLICENSETERMS="True"
/QUIET="True"
/QUIETSIMPLE="False"
/X86="True"
/ERRORREPORTING="False"
/INSTALLSHAREDDIR="C:\Program Files (x86)\Microsoft SQL Server"
/INSTANCEDIR="C:\Program Files (x86)\Microsoft SQL Server"
/SQMREPORTING="False"
/INSTANCENAME="SQLEXPRESS"
/AGTSVCSTARTUPTYPE="Manual"

/ISSVCSTARTUPTYPE="Automatic" /ISSVCACCOUNT="NT AUTHORITY\NetworkService"

/ASSVCSTARTUPTYPE="Automatic" /ASCOLLATION="Latin1_General_CI_AS"
/ASDATADIR="Data" /ASLOGDIR="Log" /ASBACKUPDIR="Backup" /ASTEMPDIR="Temp"
/ASCONFIGDIR="Config" /ASPROVIDERMSOLAP="1"

/SQLSVCSTARTUPTYPE="Automatic"
/SQLCOLLATION="Arabic_CI_AS" /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"

/FILESTREAMLEVEL="0" /ENABLERANU="True"

/SQLSYSADMINACCOUNTS="<DEKSTOP_NAME>\<ACCOUNT_NAME>"
/ADDCURRENTUSERASSQLADMIN="False" /TCPENABLED="0" /NPENABLED="0"
/BROWSERSVCSTARTUPTYPE="Disabled"

/RSSVCSTARTUPTYPE="Automatic" /RSSTALLMODE="FilesOnlyMode"

The following dialogue windows is displayed:

What would I have to change?


texts: First and last records in a string of linked rows

texts:
I have been handed an (ancient) database in SQLServer format containing rental records. These records can be modified by the customer while the rental is still in flight, if they want the drill for another 24 hours for instance. To indicate this, a new row is written to the database covering that additional period, and a pointer is left in the original record to say "this one modified me". The result is something like this:

pk   custId    prodId   overrideByPk   startDate   endDate
----------------------------------------------------------
1    1         1        0              1-1-2000    2-1-2000
2    1         1        3              1-2-2000    2-2-2000
3    1         1        4              3-2-2000    4-2-2000
4    1         1        0              4-2-2000    5-2-2000

These records represent two rental terms. The first ran for a single day in January, and the second ran from the 1st to the 5th of February. My goal is to produce output like this...

pk   custId    prodId   startDate   endDate
--------------------------------------------
1    1         1        1-1-2000    2-1-2000
2    1         1        1-2-2000    5-2-2000

It seems, based on the data I've looked at so far, that a record with a overrideByPk=0 is the last record in a string, even in a string of one row. Note that my example has separate date periods, but that's only for clarity. The customer may indeed return the item and the pick it back up the same day, so you can't look for discontinuities in the dates.

I can do this in C#  code by getting all the rows with zero, and then looking for those overrideByPk's repeatedly, but there are thousands of these and I am concerned about performance and code complexity. I seem to recall solving this in SQL in the past, but can no longer remember the trick (if it existed).


texts: How to handle versions of an Entity in Database Design

texts:
I am working on a new project. The domain logic of the project is as follows:

A user uploads a file (Document) and gives a title, then another user comes along and (s)he also tries to upload a file with the same title. This is OK providing that the body of the document has differences.

How can I design my domain model so that my web application that will later consume the data can inform the user viewing a particular document "hey there is another version of this document".

Here is a simple ERD of what I have, but that obviously does not perform what I want above.

I would greatly appreciate any help and advice.

Thanks


texts: MySQL my.cnf: innodb_log_file_size is missing

texts: How to set innodb_log_file_size to MySQL database if there is no innodb_log_file_size in my.cnf configuration file? I'd like to backup via Percona Xtrabackup and the backup crashes at this point telling me that my innodb_log_file_size is different than it expects.


texts: Need postgres statement that returns non unique records even if I pass same ids in array

texts:
For Example, the following query

SELECT id, name, price
from tennis_ticket
where id=ANY('{3,3}')

returns:

 id |     name     | price
----+--------------+-------
  3 |       Eighty |    80
(1 row)

What I want is:

 id |     name     | price
----+--------------+-------
  3 |       Eighty |    80
  3 |       Eighty |    80
(2 rows)


texts: SELECT ... FOR UPDATE returns old value after waiting for lock

texts:
It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from before the change.

Is this expected behavior or a bug? It certainly does not seem useful.

It is easy to demonstrate. Let's take the following table:

CREATE TABLE `orders`(
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(50),
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

And this sequence of statements:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

SELECT MAX(id)
FROM orders
FOR UPDATE;

# PAUSE HERE

INSERT INTO orders (description)
VALUES ('bla');

COMMIT;

Now we do the following:

Open two connections.
On connection 1, execute up to the pause. This succeeds.
On connection 2, execute up to the pause. This gets stuck waiting for a lock.
On connection 1, execute the rest. This succeeds.
Connection 2 has now returned the previous id!
On connection 2, execute the SELECT once more. Now it returns the updated value.

So to get behavior that seems sensible and desirable, it seems that we are forced to execute the SELECT twice.

Is this expected behavior or a bug?

(Tested on MySQL 5.7.25)


texts: Snapshot_isolation_state_desc NOT turning off

texts:
I am trying to resolve the following issue and need your help.

We wanted to turn on is_read_committed_snapshot_on and Snapshot_isolation_state_desc ON on our db!

When I ran following command to check current status of both above values, I got

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 0.

After this I ran two ALTER commands,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

The values updated to following,

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 1.

So we did some test we still got some locks but they we different type not the SHARED LOCK. after that I wanted to turn OFF and see if we go back to normal or not.

and I ran,

 ALTER DATABASE [<databasename>]
    SET READ_COMMITTED_SNAPSHOT off

    ALTER DATABASE <Your crm database>
    SET ALLOW_SNAPSHOT_ISOLATION off

and we got,

snapshot_isolation_state_desc  = OFF &
is_read_committed_snapshot_on = 0.

And we wanted to turn it ON again and leave it in ON status I ran,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

BUT the thing is, it is still showing the following values,

**snapshot_isolation_state_desc  = OFF &**
is_read_committed_snapshot_on = 1.

and that's my issue, that why it's still in OFF status. I restarted SQL Services many times restarted App. Server but no luck.

Please help.

Thanks.


texts: Where are tempory tables or record sets located?

texts: Using mysql and php, I would like to understand where temporary results are stored. For instance, when I do a query that returns a set of data for processing by php, is the connection to the database closed and php storing the info for use or is that info kept in the database until php has finished using it?


texts: With a recent Ubuntu upgrade it has broken my system. I now only have access to the filesystem through a live CD. I had a MySQL database on the system with many objects and views I would hate to lose. Can I recover this somehow? I don't care about the data; I just need the DDL structure of everything.


texts: Connect ORACLE to SQLSERVER to REMOTE SQL SERVER

texts:
I have Sql server and oracle installed on same machine. So I created a heterogeneous connection between the two server. Now I need to do some transaction in my sql server and need to send the result to remote SQL Server. I tried achieve this by following steps,

Created a Link between Oracle and Sql server (Works fine)
Created a Link server in Sql server to connect Remote sql server (working)
So I rewrite the Sql server procedure to call remote server.

Oracle Block

DECLARE
c             INTEGER;
nr            INTEGER;
v_sql         VARCHAR2(32767);
BEGIN

    V_SQL :='EXEC dbo.TESTPROCEDURE ''test'',1';
    C  := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@SQLSERVER;
    DBMS_HS_PASSTHROUGH.PARSE@SQLSERVER(C, V_SQL );
    NR := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@SQLSERVER(C);
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@SQLSERVER(C);
    commit;

END;

Local Sql server

CREATE PROCEDURE [dbo].[TESTPROCEDURE]
 @abc varchar(100),
 @xyx int,
 AS
DECLARE @result int
BEGIN
SET @result = 1;
INSERT INTO dbo.TEST VALUES(@abc)
/**Some code**/
EXEC @result =   REMOTESQL.[DATABASENAME].[dbo].[remoteproceduretest]  @abc, @xyz;
END

When I tried to execute the oracle block I get the following error.

  Error report - ORA-28500: connection from ORACLE to a non-Oracle
  system returned this message: ORA-02063: preceding line from SQLSERVER
  ORA-06512: at line 14
  28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"
  *Cause:    The cause is explained in the forwarded message.
  *Action:   See the non-Oracle system's documentation of the forwarded
             message.

Is it possible to call like above.

Thanks.


texts:
I am using MongoDB for a discord bot I am working on, and I want to retrieve a user's balance. My current code is
function findBalance(id){ MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("DiscordDB"); var query = { uuid: id }; dbo.collection("players").find(query).toArray(function(err, result) { if (err) throw err; console.log(result.balance); db.close(); }); }); }. When I call the function it returns undefined... any thoughts?


texts: WHERE statement returning out of bounds returns

texts:
I have this query that I'm trying to work with, and ultimately I'm trying to get it to return results as soon as it matches one of my status WHERE requirements....but that's not even my question. My question is that my query is returning a value that is TOTALLY out of bounds of my WHERE requirements, and it's just perplexing me as to why.
I have a table named leads. It has about ~30,000 rows in the table, but the specific dataset I want to be targeting is here:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    3653 |     18 | 6585993 |
|    3984 |      3 | 6585993 |
|    4100 |     18 | 6585993 |
|    8916 |     21 | 6585993 |
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Lead ID is the primary key, it's int(11) and AUTO_INCREMENTing, status is a FOREIGN KEY, and owner is a FOREIGN KEY as well.
Here is the query I'm working with:
SELECT lead_id, status, owner FROM leads
    WHERE owner = 6585993 AND (`status` = 1) OR (`status` = 21) OR (`status` = 3)
    LIMIT 1;

My ultimate goal with this query is I want to have it cascade through my last three WHERE requirements (ie, first check the owner requirement, then - if there exists no lead with a status of "1", then check for leads with a status of "21" and if no lead exists with a status of "1" or "21", then check for leads with a status of "3", and if no leads exist with any of those three statuses who also have the correct owner requirement, return none.
With how the query is written above, it returns the what I expect:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Here's the problem...when I change my status cascade to look like this (change it so that the status of "21" is to be selected first):
SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 21) OR (`status` = 1) OR (`status` = 3) LIMIT 1;

It returns this:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    9435 |      1 | 1083618 |
+---------+--------+---------+

And herein lies my ultimate question...
Why does the above query return a response with a completely wrong owner?


texts: Elastic queries across subscriptions in Azure SQL

texts:
Is it possible to run cross-database, actually cross-subscription, in Azure SQL?

I have three subscriptions (Dev, Test and Prod) and I need to need to keep Test up to date with Prod.

Unless I'm not reading the many sites correctly they seem to all assume that the databases are in the same subscription albeit possibly different servers.


texts: Unable to use select top N shortcut on Azure SQL Database via SSMS

texts:
Yesterday morning I was able to query my Azure databases via SSMS and the Select Top N menu shortcut. That afternoon I received a couple of alerts that my web applications couldn't connect to the database.

As a quick check to verify if only that database was down or if all databases on that server were down I connected to the server via SSMS. I was able to view the schema/meta data present in the Object Viewer pane without any problems. I can also execute SELECT statements against the database when I write the query and/or execute a script. However, when I try to right click a table and use the `Select Top 1000 Rows' shortcut I get an error dialog box with the following message:

  Cannot read property IsMemoryOptimized. This property is not available on Windows Azure SQL Database.
  (Microsoft.AqlServer.Smo)

Today I just noticed that my Azure SQL Database was inexplicably updated from V11 to V12. If this update happened yesterday afternoon that would be the only potential difference in configuration that I am aware of.

What can I do to suppress this error?
Why does a Select top N shortcut care about the IsMemoryOptimized property?


texts:
My CTE runs in an infinite loop for a specific customer and I am not able to find out why.

Here is the query:

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)
SELECT * FROM ClassTree


texts:
I am looking to better my knowledge without actually being in the field, I was wondering what initial steps you take when you are assuming the responsibility of a server with SQL-Server installed on it. There are a few basics like hardware inventory, and permissions, backups. But what else do you do?
I did find some decent infromation on a few forums such as...

1. Backups

Ensure all db's are being backed up(system plus user dbs)
Ensure transaction logs backups are implemented where needed (check DB Recovery Models)
Check whether backups are native backups or 3rd party(ex. Red-Gate SQLBackups or Quest LiteSpeed)
Ensure to test the db backups on a test server using restores

2. SQL Jobs / Replication / Reporting Services / Analysis Services / Integration Services

Check on all and ensure functional

3. Logs

Check SQL Server Error Logs, Windows Logs for issues

4. Security

Server-Level Security, Services Security, Logins/Users Security, Audit Security, File System Security

5. Baseline the System

Use tools like Performance Monitor, SQL Profiler, Active SPID Performance

6. Implement a Monitoring, Alerting & Performance Tool

www.sqlserverops.com, Microsoft Systems Center, www.idera.com, Quest, Red-Gate

7. Implement a Change Control System for all Production Changes

all changes go through a process(request ticket, change ticket, approvals, deployments)


texts: SQL Server 2014 Minimum Memory configuration - VMware

texts:
I am taking over a SQL Server 2014 environment which has been built and configured by a consultant.

They have set a minimum value for memory which I usually set to 0.  According to the consultant and VMware documentation the minimum memory should be set in SQL Server when it is running on a virtual machine.  I always set the Maximum leaving memory available for the OS, but do not set the minimum memory.

Any suggestions/thoughts are greatly appreciated!


texts: Can i make a relationship between two relationships in the database?

texts:
I am making a database system where there are patients, doctors, categories and medical records.

doctor and patient has a relationship called treatments ( many-to-many )
patient and a relationship with medical record called patient-records ( one-to-many )
medical record and categories has relationship called record-category (many-to-many)

My questions:

How do I include the details and information of the medical record in the treatment relationship?  Should I make a joined query and get the details of record from the patient?

My goal is to create a full record for the patient where all their history, diagnoses, and treatments are available as a sheet or web page.


texts: Can't connect to EC2 Postgresql instance unless I change the default port 5432

texts:
I just created a brand new EC2 instance with a default security group allowing All Traffic from Anywhere.

My listen_address is set to *

I can SSH to it but I can't connect to the pgsql instance running inside it, UNLESS I change the port to something other than port=5432.

This Setup Fails:

postgresql.conf:
port=5432
psql -h <public-ip> -p 5432

ERROR:

  psql: could not connect to server: Operation timed out`

This works:

port=6000
psql -h <public-ip> -p 6000

I tried this twice in two different AWS accounts, same behavior.

no iptables rules are present either, it's a brand new EC2 instance:

ubuntu@<public-ip>:~$ sudo iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

This doesn't make sense to me at all.

This is the version of Ubuntu I'm running:

ubuntu@<public-ip>:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.2 LTS
Release:    14.04
Codename:   trusty

Postgres version:

psql (PostgreSQL) 9.3.6

The weirdest thing it was working yesterday, no changes has been made except that I upgraded to Yosemite 10.10.3 (14D136.

EDIT:

I checked with another EC2 instance in the cloud as client, it connect, it seems to be my OSX, again, since I updated to Yosemite 10.10.3

EDIT 2:

Not just psql, none of my client apps are able to connect.

EDIT 3:

"host=<public-ip> port=5432 sslmode=disable" didn't worked neither.

Any tips?


texts:
Running Oracle 11GR2 instance (non-ASM). Plan to set it up again from the scratch(this time using ASM, Grid infrastructure). And I need the present database.

I am thinking of the simple plan:
1) Backup using RMAN the existing instance.
2) Deinstall the existing instance.
3) Install Grid.
4) Install Oracle DB software. (Same version)
5) Restore RMAN backup.

Is this how I should proceed, or is there any other plan of action I can look to.

Thanks


texts: Mariadb processes stuck after aborted dump restore. What to do?

texts:
I tried to restore a database dump on my local server.
I have created the database locally myself. The dump creates the tables and insert all the data.

I used 'source "C:\my_dump_file.sql"' to start the restore.
Everything goes well until nothing happens at all. No disk activity, no CPU activity, mysql is stuck waiting for something.
I killed the process (I tried CTRL-D and after that, CTRL-C), but now my mariadb installation is in an awful state (see below). It refuses to drop that database. It starts well (all my other databases work) but won't stop.

MariaDB [(none)]> show processlist;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
| Id | User        | Host            | db   | Command | Time | State                    | Info                   | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
|  1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                   |    0.000 |
|  2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                   |    0.000 |
| 10 | root        | localhost:50556 | NULL | Killed  |  677 | Unlocking tables         | DROP DATABASE failzzzz |    0.000 |
| 12 | root        | localhost:50576 | NULL | Query   |    0 | init                     | show processlist       |    0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
7 rows in set (0.00 sec)

Also, here is what the event viewer tells me every time I try to stop the mysql service

InnoDB: Starting shutdown...

For more information, see Help and Support Center at http://www.mysql.com.

InnoDB: Waiting for buf_dump_threadto exit

For more information, see Help and Support Center at http://www.mysql.com.

Please note this is actually the second time I see this issue. I encountered it for another (smaller) database, and it wrecked my mariadb installation just the same. Luckily it was my only database at the time, so I ended up throwing mariadb out of the window and starting fresh, which was the quickest thing to do. After I set innodb_buffer_pool_size to 2G for the restore, I retried and everything eventually work. There should be a better way that to try and cross fingers that the value is high enough, right ?

I'm not so lucky this time, I cannot go through a restore process for every database my mariadb is currently running (too many databases, that would be too long).
I have a recent backup of my entire data folder (prior to my restore attempts), if needed.

So, what can I do ?

how can I fix my mariadb install ? Is it possible at all ?
if it's not fixable, can I use my data folder backup to start over
fresh (WITHOUT the need to restore every database one by one, which is MUCH more time consuming). Information available online seems to indicate that it is possible (see https://dba.stackexchange.com/a/5933/147390)

Thanks


texts:
I have a question about Surrogate keys, say i have two tables(in sql server):

Table A: Student_ID, Name, School, Section
Table B: Book_ID,Student_ID, IssueDate, BookName

If Student_ID is a clustered  surrogate key and Name is unclustered index. How would one manually enter data into Table B without knowing the Student_ID. I know i might have to do a lookup in table A to find the IDs but that would not be efficient. Is there any way to make this efficient? I am trying to think how surrogate keys essentially work in terms of efficiency as a foreign key when considering inserts.


texts: T-SQL is not working on DBeaver on Linux

texts:
I am trying to run a basic T-SQL script

    DECLARE @temptable TABLE(
      abilityTypeId BIGINT NOT NULL,
      companyId BIGINT NOT NULL
    );

    INSERT INTO @temptable(abilityTypeId, companyId)
    VALUES (1, 2);

    SELECT * FROM @temptable;

but I get the following error:

  SQL Error [1087] [S0002]: Must declare the table variable "@temptable".
  Must declare the table variable "@temptable".

MERGE INTO is also not working.
Is it possible to get this working on Linux or do I have to use Visual Studio for this?


texts: How can I login to MySQL as 127.0.0.1 or ::1 instead of localhost?

texts:
I've set different password for 'root'@'localhost' and 'root'@'127.0.01' like this:
mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('pswd2');

but when I try to login like this:
mysql -u root -p -h 127.0.0.1

I still have to use the first password pswd1 even though according to this:

another password pswd2 was set for 127.0.0.1.
What is the problem here? How can I set 3 different passwords for 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1' and login to those accounts with those passwords?


texts: Can I use mysql to serve 100's of millions of small image files?

texts:
I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the small files).

For the time I have dumped the files all into large 12GB binary files and have an index that allows me to seek to the right point and read the correct # of bytes.

But that solution was a stop-gap, I need to also update the files daily, generally this means adding new files, and once-in-a-while (monthly/quarterly) do housekeeping and get rid of old ones.

So I wonder if I'll be shooting myself in the foot by trying to create a massive MyISAM table to store these 2-10k blobs?

On a 64 bit linux system there's a 4TB limit on DB file size right? Can I split such a massive table into multiple physical files as my dataset grows? Eventually we might serve 6+ TB of small image files.

My options right now are between:

Dump the binary data to a large flat file and manage the indexes on my own (either with metadata in the URL or a separate index file on the webserver)
Use a MySQL table, add new data via fast LOAD DATA INFILE, which we have had good experience with on another 100GB reference table we use.

Thoughts? Cautions?


texts: Using index on datetime field not working really

texts:
Here is my query

select msisdn, sum(score) as score
from contest_player_update_history_ideacontest6_35
where request_date >='2015-01-14 00:00:00'
  and request_date <= '2015-02-03 23:59:59'
group by msisdn;

CREATE TABLE `contest_player_update_history_ideacontest6_35` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `msisdn` varchar(15) DEFAULT '',
  `request_id` bigint(255) DEFAULT '0',
  `transaction_id` varchar(255) DEFAULT '',
  `contest_id` int(10) DEFAULT '0',
  `request_date` datetime DEFAULT NULL,
  `zone` varchar(255) DEFAULT '',
  `operator` varchar(255) DEFAULT '',
  `circle` varchar(255) DEFAULT '',
  `short_code` varchar(255) DEFAULT '',
  `channel` varchar(255) DEFAULT '',
  `language` varchar(255) DEFAULT '',
  `score` double(10,2) DEFAULT '0.00',
  `request_type` varchar(50) DEFAULT '',
  `request_value` varchar(255) DEFAULT '',
  `ans_type` varchar(255) DEFAULT '',
  `qusetion_id` varchar(255) DEFAULT '',
  `time_taken` double(10,2) DEFAULT '0.00',
  `billing_amount` double(10,2) DEFAULT '0.00',
  `billing_validity` int(10) DEFAULT '0',
  `operation` varchar(255) DEFAULT '',
  `param` varchar(255) DEFAULT '',
  `param1` varchar(255) DEFAULT '',
  `param2` varchar(255) DEFAULT '',
  `param3` varchar(255) DEFAULT '',
  `question_level` varchar(255) DEFAULT '',
  `max_allowed_question` int(10) DEFAULT '0',
  `allowed_question_per_day` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mdn` (`msisdn`),
  KEY `score` (`score`),
  KEY `request_date` (`request_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Explain gives me

 1  SIMPLE       contest_player_update_history_ideacontest6_35  range   request_date   request_date  9        (NULL)  5365990  Using where

when date range is request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' Here key request_date key is being used.

and when I change the date range just includes another day
request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-04 23:59:59', No key is being used and whole table is being traversed which causing delay in query. And data selection goes from 5 million to 20 million just adding 1 day since whole table is being traversed.

explain gives me result as

1  SIMPLE       contest_player_update_history_ideacontest6_35  ALL     request_date   (NULL)  (NULL)   (NULL)  27221681  Using where; Using temporary; Using filesort


texts: Postgresql - Can I do a join in a function?

texts:
I'm recently starting to use PostgreSQL. In SQL Server I used to use stored procedures for different kind of things. I want to know if, like in SQL Server, can I do something like in that.
I used to use this kind of things;

create procedure my_proc
@opt int=null,
@param varchar=null
AS BEGIN
IF @opt = 1
 select * from my_table1 t1
 inner join table2 on t1.id=t2.id
 where t2.param=@param
END IF
IF @opt = 2
select "nothing"
END IF

GO

If the user return the @opt variable with 1 the result could be like:

t1_id | t2_id | param
2     | 2     | qer
5     | 1     | qer
8     | 1     | qer

and if the user return a 2 in the @opt it would be something like:

nothing


texts: How can I get nodetool without Cassandra

texts:
I have Cassandra pool across machines in LAN. I need nodetool on machine without Cassandra installed. How can I get it?

Is the installing Cassandra and disabling db service is the best way?


texts: Installing SSIS package outside of SSIDB catalog

texts:
I'm trying to simplify deployment of SSIS package to a remote server. At the moment, I have

Local Visual Studio SSDT and SSIS package that has 4 connection managers - DEV environment
Remote server with SQL, SSMS, and SSDT installed - PROD environment

The easiest way to deploy SSIS package from local computer to the remote server is to

Create SSIDB catalog on the remote server
Perform Deploy from Visual Studio and point it to the catalog created on the remote server

In this case everything works fine.
In the meantime, some remote servers may not be accessible from Visual Studio directly, so I'm trying to precompile SSIS package on local computer, upload to the remote server and add it to SQL server job as a single file, like it's described in this article. The second screenshot in that article shows that I can choose Package Source = File System and specify a path to SSIS package file.
The issue
When I deploy SSIS package from Visual Studio to SSIDB catalog and create SQL server job using package in the catalog, it correctly shows 4 connection managers used by the package. When I create SQL server with manually uploaded SSIS package file, the tab Data Sources in SSMS shows empty list of connection managers. Why in the second scenario, connection managers were not deployed with the package?


texts: SQL Server 2008: Diff table data on different servers with exact schema

texts:
I'm trying to find a good open source tool that simply compares data in a database on one server to a table (of the same schema) on another server.  Then I want to generate INSERT statements for the rows that are different, for either the source or destination/target table.

I've tried downloading several tools, and I can't get any of them to work because they all had bugs.  I can be more specific with the errors I received if you need me to be.

I've tried:

TableDiffGui.zip
SqlDiffFramework setup 1.0.3.0.exe
SqlTableCompare.zip
DaBCoS.0.3.2163.zip

If there is a manual way to do this, please let me know.  My last resort will be to simply write a program that does it.


texts: Adding Partitions to an Existing Filegroup

texts:
For maintenance reasons, the organization that controls the servers where I work has decided to limit the number of file groups and files for partitioning to just 1 of each.  Their reason for this is because of improved storage technologies recently put in place to eliminate the physical storage space allocated in the .ndf files without having to run shrinks.

Our partition scheme has 100 partitions beyond the current date.

My question is how do I expand the boundary keys for day 101 and beyond to the same partition scheme WITHOUT having to name a new file group and .ndf file?


texts:
I have a table which contains this data:

- ID - PRIMARY KEY, INTEGER
- PC_ID - INTEGER
- USER_ID - INTEGER
- PAYMENT_DATETIME - TIMESTAMP
- FIO - VARCHAR2(255)

When I do a SELECT I get this (example):

1, 1, 1, 08.06.2018 17:33:26, Test
2, 1, 1, 08.06.2018 17:34:52, Test
3, 1, 1, 08.06.2018 18:01:33, Test
4, 1, 1, 08.06.2018 18:03:17, Test2
5, 1, 1, 08.06.2018 19:26:41, Test
6, 1, 1, 09.06.2018 13:22:58, Test2

So if I group them by PC_ID, USER_ID, Distinct FIO, DATE (DD. MM. YYYY), TIME (HH24) as rows to show amount of different FIO for hours 07 to 22 for each day for each user for each pc, I get:

1, 1, 08.06.2018, 17, 1
1, 1, 08.06.2018, 18, 2
1, 1, 08.06.2018, 19, 1
1, 1, 09.06.2018, 13, 1

The last column is the Count of distinct FIO.

Now I would like to transform it to:

Columns: PC_ID, USER_ID, DATE (DD. MM. YYYY), TIME (HH24) for 07 to 22 and TOTAL FOR DATE

If the count for hour is 0 then show 0:

So the structure should look like:

PC_ID, USER_ID, DATE, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, TOTAL_FIO

I was thinking of CTEs, can this be achieved using them?


texts: SQL Server 2012 Create index with Sort In TempDb On - Gains False?

texts:
If I create an index e.g

Create nonclustered index(Path, Value, Id) with(sort_in_tempdb=on);

It doesn't fail but when looking at the index in SMMS, it says False. Any ideas why?

EDIT: Can not find any schema to query for the value of this setting. Tried sys.Indexes but with no luck.

//Daniel


texts: how to use a variable as a part of a field name in mysql trigger declaration

texts:
Here is what I want to do (a part of my trigger):

BEGIN
DECLARE updatecount INT default 0;
declare number int;
declare column varchar(10);
declare i int default 1;
declare j int default 1;
while i<=6 do
    set column = "num"+i; /*will be num1 num2, so on*/
    set number = NEW.@column; /* will represent NEW.num1, num2 so on*/

I know that the last 2 lines are not correct. But I wanted you guys to understand what I want to do. Can anyone tell me the correct syntax? thanks.

EDIT:

what I want to do with this trigger is when a new row added into the resultsTBL, I want to check all the rows in the guessesTBL to find how many of them are same.

For example we have 2 rows in guessesTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    5    10   15   20   26
 4    8    12   16   25   40

then we add a new row into the resultsTBL (always in ascending order):

num1 num2 num3 num4 num5 num6
 1    8    17   20   25   40

after that trigger will compare the new added row with all the rows in the guessesTBL:

for the 1st row the result is: 2
for the 2nd row the result is: 3

pseudocode:

CREATE DEFINER = CURRENT_USER TRIGGER `myDB`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
for each field in resultsTBL (num1, num2, ... num6)
  number= num1...num6
  for each field in guessesTBL (num1, num2, ... num6)
    if number == guessesTBL.num1,num2,...num6
      counter++


texts:
I am migrating a SQL Server 2008R2 database to a new server. (also 2008R2)

There are a number of CLR assemblies on the server. Will these automatically migrate with the database or will I have to manually script them?

Thanks!


texts: How to change the reporting services report service mode from Native to integrated?

texts:
In SSRS-2016, I am trying to change the Reporting  services report service mode from Native to integrated.

I added 2 new shared features to existing installation (See in the Pic)

I am trying to change the report server mode from native to integrated. I opened Reporting services configuration manager and add new report serve and add a database I am seeing just native mode option.

I am not seeing integrated option.

could you please suggest me on how I can make the report server mode to integrated?

I don't see that option. (I restarted after installing the 2 features)


texts:
While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:

having main_table (potentially above 1000 mln rows) and update table up to a few million rows. Data in update are indexed according to main_table (have corresponding id columns).

copy rows from main_table into temp_table where id not in update id table  (where data has actually changed).
simple append of the update table onto temp_table
drop main_table
rename temp_table to main_table
renumerate main_table pk
create the next update with python and repeat.

The above is to be done with the following sequence of queries:

CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));
INSERT INTO temp_table (listing_id,date,available,price,parsing_timestamp)
    (SELECT listing_id,date,available,price,timestamp
     FROM   update c);
DROP  TABLE main_table cascade;
ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table DROP COLUMN id
ALTER TABLE main_table ADD COLUMN id SERIAL PRIMARY KEY;
DROP  TABLE update cascade;

While debugging the queries, I have found the slow queries with explain:

EXPLAIN (ANALYZE, BUFFERS) CREATE TABLE temp_table (like main_table including all);
INSERT INTO  temp_table (listing_id,date,available,price,timestamp)
    (SELECT listing_id,date,available,price,timestamp
         FROM   main_table c
         WHERE  NOT EXISTS (SELECT
                            FROM   update
                            WHERE  id = c.id));

This is the result of EXPLAIN (ANALYZE, BUFFERS) as requested in comments:

QUERY PLAN
Merge Anti Join (cost=513077.42..4789463.48 rows=109800833 width=40) (actual time=1216.018..48520.564 rows=112757269 loops=1)
Merge Cond: (c.id = update.id)
Buffers: shared hit=4 read=1359891 written=2838, temp read=8701 written=15077
-> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) (actual time=0.857..29573.798 rows=113119497 loops=1)
Buffers: shared hit=1 read=1330192 written=2838
 -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) (actual time=1215.147..1260.191 rows=362229 loops=1)
Sort Key: update.id
Sort Method: external merge Disk: 58480kB
Buffers: shared hit=3 read=29699, temp read=8701 written=15077
-> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8) (actual time=0.179..423.100 rows=3318663 loops=1)
Buffers: shared read=29695
Planning Time: 0.259 ms
Execution Time: 52757.349 ms

How to optimize the queries?


texts: Why does sqlcmd truncate varbinary(max)?

texts:
Inside of the sys.assembly_files catalog, I can see a field named content

1> SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
2> GO
assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x07585

(1 rows affected)

However, even with sqlcmd -Y 8000 -y 5000 I can't get more than 0x4D5A90x075850x167D00x043CB

assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x075850x167D00x043CB

Wondering what was going on and suspecting the client wasn't returning everything to me, I tried mssql-cli, the port of pg-cli to SQL Server. Using mssql-cli, I saw the entirety of the content field, which does in fact look to be the right size to be the bit contents. That leaves me to believe this is a problem in sqlcmd.exe truncating, or other mutilating and adulterating the bytecode representation of the assembly. Using mssql-cli, I get this

master>SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
......
-[ RECORD 1 ]-------------------------
assembly_id      | 1
(No column name) | microsoft.sqlserver.types.dll
file_id          | 1
content          | 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000D00000000E1FBA0E00B409CD21B........ and on and on
(1 row affected)


texts: SQL Server 2008 R2 Service - large memory consumption

texts: I have a SQL Server instance where the service is consuming 173 GB, the max server memory is set at 60 GB. How do I determine where that memory is or if I have a memory leak?


texts: Optimality of Columns vs. Complementary Table in MySQL

texts:
I'm starting work on a project where I'll have a table of entries as well as a table of descriptors.

The entries will be updated infrequently, if ever, and the descriptors table will be largely static. Each entry will have approximately 5-25 descriptors linked in an associative table, though it could be more or less. Each entry has five core columns and another 20-ish nullable columns for optional metadata, mostly tinyints, though one is a free-text input that I currently have as VARCHAR(4096). I would expect an average of 5-10 of these columns to be completed per entry, though none are mutually exclusive, and thus it could be all or none.

Should I leave the 20-ish metadata columns in the entry table, or should I make integer-based keys for each and put them in a separate table? How significant would any performance difference be? I realise that this may be premature optimisation, but I'd rather avoid potential headaches down the road.


texts: Oracle 11g how to change nls_characterset only for one table or tablespace

texts:
Is it possible to change nls_characterset only for one table?
If it not, could I change or create new tablespace with different nls_characterset?


texts: Subquery for column names of tablefunc crosstab queries

texts:
Is it possible to get the values for the names of the columns for the crosstab query from a subquery?

CREATE TABLE mytable(employee_name text, month date, allocation double precision);
INSERT INTO mytable VALUES('A','2017-11-30','0.5');
INSERT INTO mytable VALUES('B','2017-11-30','0.8');
INSERT INTO mytable VALUES('B','2017-12-31','0.5');

SELECT * FROM crosstab
(
  'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
  'SELECT DISTINCT month FROM mytable ORDER BY 1'
)
AS
(
    employee_name text
    # Here a subquery to get column names
    # automatically eg : 'SELECT DISTINCT month FROM mytable'
);


texts: postgres system catalog query columns from table

texts:
I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when I try the following script:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

I get a return of 0, as I should since this user does not have permission.

I am trying to query the database to verify the tables and the columns created. I can get the list of table names through the system catalog with the following script:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = '{schema}'

And this outputs the table names the way I want it:

  business, location, person, etc...

I can't find the script with system catalog to then find the column names (And as a bonus, the data type) of each table. So far I've tried the following:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE  attrelid = 'business'

and here is the error:

ERROR:  invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'
                                                         ^```

Also tried:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
            --AND pg_catalog.pg_table_is_visible(c.oid)
    );

and this returns 0 with a schema or database. I'm not sure what to put there for c.relname. Am I also seeing 0 with this because a basic user just cannot see deeper than tables in a schema, period?


texts: How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

texts:
If I have foo.sql and bar.sql in the same folder, foo.sql can reference bar.sql when run from SSDT in sqlcmd mode with :r ".\bar.sql". However, SSMS won't find it. Procmon shows SSMS is looking in %systemroot%\syswow64:

How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?


texts: How can I measure the impact of queries with errors being rejected by SQL Server?

texts:
After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). The errors can be classified on the 1st and 2nd steps for Processing a SQL Statement:

The DBMS first parses the SQL statement. It breaks the statement up
into individual words, called tokens, makes sure that the statement
has a valid verb and valid clauses, and so on. Syntax errors and
misspellings can be detected in this step.
The DBMS validates the statement. It checks the statement against
the system catalog. Do all the tables named in the statement exist
in the database? Do all of the columns exist and are the column
names unambiguous? Does the user have the required privileges to
execute the statement? Certain semantic errors can be detected in
this step.

The doc also says that Parsing a SQL statement does not require access to the database and can be done very quickly, but the amount of requests being fired at the server made me think I should verify.
I’d like to measure the resource consumption impact caused by those wrong requests being rejected on those steps. Is there a way to do so?


texts: MYSQL Event was executed but no effect to database

texts:
How do we troubleshoot MYSQL scheduled event that did not work as expected?

We got numerous events on our database.
All these event that have been scheduled and executed by event scheduler.

This event auto_delete_last_order_event would call a store procedure. If the store procedure was run manually , it successfully affected the table.

On INFORMATION_SCHEMA.events , it states all my events are working fine.
My other events are  affecting the database but only this auto_delete_last_order_event event does not affect the table.

On manually run the store procedure , it does affect database and it takes less than 0.5 seconds to complete.

UPDATE 1:
SHOW CREATE PROCEDURE db.auto_delete_lastorder

           Procedure : auto_delete_lastorder
sql_mode :STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character_set_client : utf8
collation_connection : utf8_general_ci
 database_collection : latin1_swedish_ci

Create Procedure :

CREATE DEFINER=`root`@`localhost` PROCEDURE `auto_delete_lastorder`()
 BEGIN
declare totalLastData int;

    set totalLastData = 2;

    DROP TEMPORARY TABLE IF EXISTS tempData;
    CREATE TEMPORARY TABLE tempData
    select join_id from lastorder jl
    where not exists(
        select join_id from
        (
            select user_id,join_id
            from
            (
              SELECT user_id,
                join_id,
                (@row:=if(@prev=user_id, @row +1, if(@prev:= user_id, 1, 1))) rn
              FROM lastorder tt
              CROSS JOIN (select @row:=0, @prev:=null) c
              order by user_id, join_id desc
            ) src
            where rn <= totalLastData
            order by user_id, join_id
        )tt
        where jl.join_id = tt.join_id
    );

    delete lastorder  FROM lastorder
            JOIN
        tempData ON lastorder.join_id = tempData.join_id;

 END


texts: What can cause log shipping copy step to occasionally fail with access denied?

texts:
Have sql agent job for log shipping copy step that fails every once in a while with "Reason: access is denied" but no other detail.
Here is what the failing step looks like:
"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe" -Copy DEF49099-4526-ABCD-83AE-E00F5C221E74 -server MyServer

Job runs every 15 minutes but fails maybe once a day. SQL Agent service running as domain user. How can I drill down further on this intermittent failure.

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0
(X64)      Standard Edition (64-bit) on Windows NT 6.3  (Build
9600: )


texts: Nested json response from 3 one to many tables

texts:
I am designing data model in following way:

  Table 1:
          id: number
         somedata: String
         somedata: String

  Table 2:
         id: number
         Table1_id: number
         somedata: String
         somedata: String

  Table 3:
        id: number
        Table2_id: number
        somedata: String

1st table is joined to 2nd table with one to many relationship. 2nd table is joined to 3rd table with one to many relationship. I am using java, JPA. I have to return JSON via rest api in following format:

{
  "table1_id": 1,
  "somedata": "somedata",
  "table2_data": [
    {
      "table2_id": 1,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 1,
          "somedata": "somedata"
        },
        {
          "table3_id": 2,
          "somedata": "somedata"
        }
      ]
    },
    {
      "table2_id": 2,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 3,
          "somedata": "somedata"
        },
        {
          "table3_id": 4,
          "somedata": "somedata"
        }
      ]
    }
  ]
}

If I use mysql and above table structure I will need to use minimum 3 database calls to fetch from 3 tables separately and will have to construct my json. Is there a way where I can reduce database calls and also reduce effort to construct my json? I am open to change database(mysql) also. I just need fast, optimised and best solution. Note that table1, table2 and table3 has different fields so I can not denormalize them to single table.

I already have seen this question and my question is different than this: https://stackoverflow.com/questions/11047151/nested-json-from-3-one-to-many-tables


texts: SQL Server Restore DB from MDF file, lost LDF and DB was not cleanly shutdown

texts:
My dev DB server was formatted by some admin without my knowledge. Thankfully I had mdf file kept in some other drive but ldf was in its default location. Since I don’t have DB backup I am now left with mdf file. I googled and found various tricks to restore DB from mdf file. Since database was not cleanly shutdown none of the tricks worked. I have SQL Server 2008 sp2 installed in my machine. As I remember lost DB server was SQL Server 2005 instance. Please let me know how I could recover my database.

Tricks i tried:

I tried to attach the mdf from SSMS by removing the log file item from database to attach list.
CREATE DATABASE Monitoring ON (FILENAME = N'D:\Dump\Data\Monitoring.MDF') FOR ATTACH_REBUILD_LOG
sp_attach_single_file_db N'Monitoring', N'D:\Dump\Data\Monitoring_Data.MDF'

In all above I got

  Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

Then I tried DBCC REBUILD_LOG('Monitoring',N'C:\Program Files ..MSSQL\Data\Monitoring_log.ldf') which also resulted in

  Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options


texts: Master database is corrupt, instance won't start - what are my options?

texts:
Help! My master database is corrupt, I can't even bring the SQL instance online! What are my options to get my server back up?

I do have a backup of master, but the MSDN page "Restoring the master Database" asks me to start the instance in single-user mode, which I can't do!

(Note: I'm leaving this question unspecified as to SQL version, so as to be a more widely-applicable reference. There are some similar questions on DBA.SE, but none that involve the server unable to start.)


texts: Initializing Transactional Replication From Backup

texts:
There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from a backkup but have never set this flag (we just noticed it for the first time a couple of days ago). The replication certainly has worked without issue all this time.

I've found a lot of hits explaining that this needs to be used but none that explain why.

Does anyone have an understanding of what this actually does? From my vantage, it doesn't seem to be necessary but I'm thinking I have to be missing something.


texts: How do I get the Query Timing with sqlcmd?

texts:
Running SQL Server 2017, using the interactive REPL client sqlcmd how do I get the time for Query Execution?

You can sp_BlitzErik showing this in his answer here,

SQL Server Execution Times:
  CPU time = 1859 ms,  elapsed time = 321 ms.


texts: While inserting to table date going as 000000 in mysql

texts:
I have a table with column datatype as Date in mysql database.
I want to insert dates in 'dd/mm/yyyy' format so I used below query. When I use only the SELECT, the result set is showing correct. But when I use the INSERT, it is going as '000000' (the values inserted are all the '0000-00-00' date in abc).

The column I am selecting from (completion_date) is varchar datatype.

INSERT INTO abc
    (maturity_date) -- Datatype Date for maturity_date column
SELECT
    DATE_FORMAT(
        STR_TO_DATE(completion_date,    -- varchar
                    '%d/%m/%Y'),
        '%d-%m-%Y')
FROM XYZ ;

When if am using below script, data is inserted properly but format is 'yyyy/mm/dd'.

INSERT ...
SELECT STR_TO_DATE(completion_date , '%d-%m-%Y')
...

Is there any way to insert in 'dd/mm/yyyy' format with proper data?

example: '21-01-2017'


texts: DECIMAL and NUMERIC datatype in Postgres

texts:
what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes.

decimal,numeric --> It is a user specified precision, exact and range up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

The above statement shows the description of decimal and numeric datatype. But, still I didn't understand what is the
    exact use of these data type and where it is used instead of other datatypes.

Answer with example is much appreciated...


texts:
I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed that the export from sqlplus is twice as large as the export file from SQL Developer. The contents/result of the files are exactly the same.  Here are my  sql file called oracle_sql.sql

set embedded on;
set pagesize 0;
set colsep ',';
set echo off;
set feedback off;
set linesize 1000;
set trimspool on;
set headsep off;
set termout off;
set heading on;
set und off;
spool mytable.csv
select item, item_desc
from mytable;
spool off

here are my .bat file code

@echo off
sqlplus jasons/jasons@localhost @oracle_sql.sql

Do I need to have an extra setting in my sql file that I am not aware off?


texts: How to hint many-to-many join in SQL Server?

texts:
I have 3 "large" tables that join on a pair of columns (both ints).

Table1 has ~200 million rows
Table2 has ~1.5 million rows
Table3 has ~6 million rows

Each table has a clustered index on Key1, Key2, and then one more column. Key1 has low cardinality and is very skewed. It is always referenced in the WHERE clause. Key2 is never mentioned in the WHERE clause. Each join is many-to-many.

The problem is with cardinality estimation. The output estimation of each join gets smaller instead of larger. This results in final estimates of low hundreds when the actual result is well into the millions.

Is there any way for me to clue the CE into making better estimates?

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Solutions I have tried:

Creating multi-column stats on Key1, Key2
Creating tons of filtered stats on Key1 (This helps quite a bit, but I end up with thousands of user-created stats in the database.)

Masked execution plan (sorry for the bad masking)

In the case I'm looking at, the result has 9 million rows. The new CE estimates 180 rows; the legacy CE estimates 6100 rows.

Here's a reproducible example:

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2),
     DataSize (Key1, NumberOfRows)
     AS (SELECT 1, 2000 UNION
         SELECT 2, 10000 UNION
         SELECT 3, 25000 UNION
         SELECT 4, 50000 UNION
         SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
     , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
     , T1Key3
FROM DataSize
     CROSS APPLY (SELECT TOP(NumberOfRows)
                         Number
                       , T1Key3 = Number%(Key1*Key1) + 1
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT
       Key1
     , Key2
     , T2Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1*10)
                         T2Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT
       Key1
     , Key2
     , T3Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1)
                         T3Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

DROP TABLE IF EXISTS #a;
SELECT col = 1
INTO #a
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1
INTO #b
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN #Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;


texts:
I have a table with a point data type that stores the latitude and longitude.  I plan to insert from a function, and the input argument is defined as a float(18) [which is where I suspect the problem comes from].

Let's say the following values are used:

48.277274, -116.5547745

If I insert these with from the command line the database shows those values.  But if I insert them from the function the values in the database are:

48.2772750854492, -116.55477142334

I'm not doing anything to modify the values, as I mention above I believe bringing them in as float is causing the problem, but I've tried various other types and only get other issues.

Would greatly appreciate any help.

CREATE OR REPLACE FUNCTION myfun(
    float(18), --   lat
    float(18)) --  lon
  RETURNS integer AS
$BODY$
DECLARE
    postcnt integer;
BEGIN
   INSERT INTO user(usergeo)
   VALUES (point($1,$2))
   RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


texts: Find uncompressed size of all tables in a database

texts:
In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'm talking about is called entiretablecache and loads the whole table in memory as soon as a single record is requested.

Up to recently we relied on some scripts to verify the size of the tables that have this setting to see if the table size is above this limit.

Now however, compression comes into play and things like sp_spaceused or sys.allocation_units seem to report the space actually used by the compressed data.

Obviously, the application server is working with uncompressed data so the data size on disk in SQL Server is irrelevant. I need the actual size the uncompressed data will have.

I know of sp_estimate_data_compression_savings but as the name says, this is just an estimate.
 I would prefer to have size as correct as possible.

The only way I could think of was some convoluted dynamic SQL creating uncompressed tables with the same structure as the compressed tables, inserting the compressed data in that shadow table and then check the size of that shadow table.
Needless to say, this is a bit tedious and takes a while to run on a database of several hundreds of GB.

Powershell could be an option, but I wouldn't like to iterate over all tables to perform a select * on them to check the size in the script as that would just flood the cache and would probably take a long time too.

In short, I need a way to get the size for each table as it will be once uncompressed and with fragmentation out of the equation as presented to the application, if that's possible. I'm open to different approaches, T-SQL is preferred but I'm not opposed to Powershell or other creative approaches.

Assume the buffer in the application is the size of the data. A bigint is always the size of a bigint, and a character data type is 2 bytes per character (unicode). BLOB data takes the size of the data too, an enum is basically an int and numeric data is numeric(38,12), datetime is the size of a datetime. Also, there are no NULL values, they are either stored as an empty string, 1900-01-01 or zero.

There is no documentation on how this is implemented, but the assumptions are based on some testing and the scripts used by PFE's and the support team (which also ignore compression apparently, since the check is built in the application and the app can't tell if the underlying data is compressed) which also check the table sizes. This link for example states:

  Avoid using EntireTable caches for large tables (in AX 2009 over 128
  KB or 16 pages, in AX 2012 over ‘entire table cache size’ application
  setting [default: 32KB, or 4 pages]) – move to record caching instead.


texts: Convert all ntext columns to nvarchar(max)

texts:
I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max).

This is the code

ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;

  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR

    SELECT so.name as table_name, sc.name as column_name
      FROM sys.objects so
      JOIN sys.columns sc ON so.object_id = sc.object_id
      JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                        AND stp.name = 'ntext'
     WHERE so.type = 'U' -- to show only user tables
  OPEN tables_cursor

  FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE Eventlogic.dbo.' + @table_name + ' ALTER COLUMN ' + @column_name + ' nvarchar(max);')
    EXEC ('UPDATE Eventlogic.dbo.' + @table_name + ' SET ' + @column_name + '=' + @column_name + ' ')
    SET @count = @count + 1;
    IF @count > 0
      PRINT ('Eventlogic.dbo.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  END
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))

END;

Whenever I try to run it, I get this error:

  Msg 16924, Level 16, State 1, Procedure usp_SL_ConvertNtextToNvarchar, Line 37
  Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

We have 338 columns across different tables as ntext, due to legacy.

We upgraded to SQL Server 2008 R2, so we would like to convert these columns to nvarchar(max).

We want to follow the advice here ntext vs nvarchar(max) where Conwell suggests to do the update after the alter.

  SQL server moves the text from the LOB structure to the table (if less than 8,000 bytes).  So when we run the select again with IO STATISTICS we get 0 LOB reads.

Any help with this error would be great.

UPDATE

Updated code as Martin mentioned.
It is actually altering only the first one on the list before giving the error.

2nd UPDATE

After making the changes on the code to fix the second fetch, I closed SQL Management Studio.
Opened SQL Management Studio again and run it and it worked. So thanks Martin again.

Thanks in advance.

Federico


texts: Restoring a mysql database using --single-transaction

texts:
I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the database from my dump file while also utilizing transactions. That is I have no idea how to make the dump file in such a way that it's execution will restore the dumped databases in a single transaction, without issuing any locks.

Anyone encountered such problem?


texts:
I have problem in selecting a row in child sp which just got updated in parent SP.

MySP updates MyTestTable's MonkeyColumn, and right after it want to return newly updated row to the user by calling to the GetMonkey procedure, but for some reason monkeyColumn is still empty. I know that i can use Output parameters, but then MySP will only return output parameters and not the result of GetMonkey stored procedure. What have i done wrong?

Create PROCEDURE [dbo].[MySP]
    AS
 Begin
      declare @testid uniqueidentifier
    select @testid = [dbo].GetTestIDFromParams(@someValue1)

        Update MyTestTable
         set MonkeyColumn = 'monkey',
             where id= @testid;

        exec [dbo].GetMonkey @testid

  END

GetMonkey contains :

Create PROCEDURE [dbo].[GetMonkey]
    -- Add the parameters for the stored procedure here
@testid uniqueidentifier
AS
Begin
SET NOCOUNT ON;
Select MonkeyColumn from MyTestTable where id= @testid;

END


texts: Changing MASTER_CONNECT_RETRY - Anything pit falls to keep in mind?

texts:
We run your basic Master + Multi-slave mysql replication setup.

We would like to lower the MASTER_CONNECT_RETRY parameter on each of our slaves.

Is there anything beyond re-executing CHANGE MASTER TO ... on each of our slaves with the new connect_retry paramater (and of course MASTER_HOST, MASTER_USERNAME, etc etc, which will be the same) that we need to keep in mind?

As mentioned, this is an already running replication setup. All that we want to do is lower the MASTER_CONNECT_RETRY time.

Thanks.


texts: Installing SQL Server 2000 x86 Enterprise on Windows 2003 x64

texts:
I have inherited (lucky me) a very old accounting system which will be retired over the next 18 months.
In the meantime, for various reasons, I need to upgrade the DB server.

It's currently running SQL Server 2000 Sp4 Enterprise on Windows 2000 Advanced Server.

The only supported OS I can get to without upgrading the whole application (too costly) is Windows 2003 x64.

I plan to put it on a VMware server, Windows 2003 x64 Enterprise Edition, with SQL Server 2000 SP4 Enterprise Edition x86. The virtual server will have 8GB Ram allocated to it and 4 vCPUs.

Is Sql Server 2000 x86 on Win 2k3 x64able to access the full 8GB of the OS (using PAE or similar?)


texts: Insert record only if a combination of column values doesn't exist

texts:
I have a simple MYSQL insert query:

INSERT INTO table('col1','col2','col3') VALUES('123','456','some text')

I want to insert only if the above combination of columns does not already exist and this case is only when col1 has value of 123 and col3 has values of some text

I will try to explain with some examples of what is allowed and what not:

('123','456','some text') allowed ONLY if not exists

('123','847,'some text') allowed ONLY if not exists

Both results above can coexists together because col2 is different. However, each of the above results can exist only once

The following ones can be duplicate because they don't have the combination of col1 and col3 of the values 123 and some text respectively

('412','847','some text') allowed no matter what even if exists

('763','372','any text') allowed no matter what even if exists

To summarize, a combination of col1,col2,col3 should be unique ONLY when col1 has value 123 and col3 has value some text

The primary key of the table is an auto_increment id.

Can this be achieved by setting certain indexes on the table or only through subqueries or what are the solutions available here to achieve this?


texts: SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

texts:
for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    IF EXISTS(SELECT 1 FROM tblCustomer WHERE CustomerID = @CustomerID)
        BEGIN
            UPDATE tblCustomer
            SET
                CustomerName = @CustomerName
            WHERE
                CustomerID = @CustomerID;
                SELECT @New_ID = @CustomerID;
        END
    ELSE
        BEGIN
            INSERT INTO tblCustomer(
                Taalnaam)
            VALUES(
                @CustomerName)
                SELECT @New_ID = scope_identity();
        END
    END

into a syntax using the MERGE statement:

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    SELECT @New_ID = @idTaal;

    MERGE dbo.tblCustomer as target
        USING (SELECT @CustomerID, @CustomerName) as source (CustomerID, CustomerName)
        ON (target.CustomerID = source.CustomerID)
        WHEN MATCHED THEN
            UPDATE SET
                CustomerName = @CustomerName
        WHEN NOT MATCHED THEN
            INSERT (CustomerName)
            VALUES(source.CustomerName);
                SELECT @New_ID = scope_identity();
    END

Question:

is this going to benefit us performance wise? (avoiding the initial SELECT on the primary key)
is this proper use of the MERGE statement? Most examples show MERGE statements in a scenario to execute multiple DML operations.


texts:
Talking about transactions (db transactions), does a read operation have always precede a write operation?

That's obvious, isn't it?

Thanks in advance

*Of course my question doesn't take into account any specific locking protocol or whatever it's actually related to the following question:

A transaction T is defined by a partial order, termed <, such that:

if r[x] and w[x] belong to T, then r[x] < w[x].

Thanks again.


texts: Clustered Index - Trade-off Queries VS Inserts?

texts:
I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask.

My application caveats

In my application I have the concept of Show.

This means that a user can create/open a Show and then it will access only the objects related to that Show (Actors, clothes, items, etc). So it is really commom to have queries using "...WHERE ShowId = X"

Also we do have quite a lot of inserts because of integrations (we have a job running daily that would get +/- 30k new rows to the database, all in the same Show) and deletions are not that commom... The user may delete a Show (which is really taking quite a long time).

The usage of the application is usually like:

User creates a Show based on a "Master Show" (which copies a lot of data changing only the ShowId)
User navigates around some screens to check this data
User calls an algorithm that proccess the input of this show and generate a lot of outputs (30k+ inserts again, on the same show)
User analyses these outputs

The question

Based on that, I thought that the best clustered key would be on [ ShowId, id ] for every table. But then, reading this documentation, I have doubts if this wouldn't make it worse given the number of inserts or if, because the inserts are made on the same ShowId, it would be ok.

I don't have access to the PRD database nor to a copy of it for testing. I tested with the dev database but it is so small it didn't seem to have any impact at all.

Can someone help?


texts: How do I reference a row which has two foreign keys as its primary key?

texts:
I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.

As every team combination can only exist once, I decided that the primary key of each team is composed of the two ids of the players in it:

CREATE TABLE teams (
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2)
);

The competition consists of matches, each match being three games.

I want to reference two teams in a match:

CREATE TABLE matches (
  id INTEGER PRIMARY KEY NOT NULL,
  team_1 INTEGER REFERENCES Team(id) NOT NULL,
  team_2 INTEGER REFERENCES Team(id) NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)

Each game contains the score of the winning and the losing team:

CREATE TABLE games(
  id INTEGER PRIMARY KEY NOT NULL,
  match_id: INTEGER REFERENCES Match(id) NOT NULL,
  score_winner : INTEGER NOT NULL,
  score_loser : INTEGER NOT NULL
)

As I do not know the winner of a game like this, I would like to add the primary key of the team that won the game to be able to to figure out the winner of a match (whoever won the most matches)/

How do I reference the winner of a game in the given table?


texts:
I want to check if my Data Warehouse historical filegroup backups are valid with my most recent Primary Filegroup backup.

I backup the Primary Filegroup every night but historical filegroup backups may be months old. I know in theory it should all work as long as the historical filegroups remain Read-Only but I want to CHECK.

I hope this makes sense - its a bit hard to get across but I worry that in a disaster my historical filegroups won't restore with my latest Primary backup.


texts: SQL Server - Query Over Linked Server to IBM DB2 throws an error

texts:
We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server.
It's been working great for a long time, but over the weekend they upgraded their system, and now one of our jobs is failing.

The linked server still connects fine, meaning that if I look at the server under Server Objects->Linked Servers->servername it shows up, and I can see all the tables.

When I run the query through an OpenQuery it works fine:

  Select * From OpenQuery(GLDATA, 'Select WPCPKG, WPCBAC, WPCDEN, WPCPRT from S782D480.GLDATA.GPCKCTL') --This works

But when I use the four part naming that is needed to do an insert into that table, it fails:

  select WPCPKG, WPCBAC, WPCDEN, WPCPRT from GLDATA.S782D480.GLDATA.GPCKCTL -- fails, but used to work last week

The error says:

  OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "Unspecified error".
  OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - SYSCOLUMNS in QSYS2 type *FILE not found.".
  Msg 7311, Level 16, State 2, Line 7
  Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "MSDASQL" for linked server "GLDATA". The provider supports the interface, but returns a failure code when it is used.

Per this article I've tried dropping and recreating the linked server, and turning "allow inaccess" off and on in the provider, but that didn't work.

The error is vague enough that I'm not finding much information to try, and I suspect that it is a config problem on their end, but unless I give them the solution on a platter they are very slow to fix things.


texts: Of the multiple cnf files in /etc/mysql/conf.d/ dir, which would be the last one to be read (and will eventually take precedence)?

texts:
For some reasons (and only for some days to come) we are going to have 2 cnf files in /etc/mysql/conf.d directory, apart from the default one /etc/mysql/my.cnf. Now I know this can be disastrous, and we are working to resolve this.

But for the sake of knowledge ....

The config of /etc/mysql/my.cnf will be over-ridden by the cnf file in /etc/mysql/conf.d/

But between the 2 cnf files in /etc/mysql/conf.d, lets say a.cnf and b.cnf, which one would take precedence and why?

MySql version is "Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1"


texts: IBM DB2 Timetravel logging based on some criteria

texts:
I have been searching for the condition, where, lets say when we enable time travel to a certain table in DB2 , but don't want to capture all the updates done, but only the updates that's done  by some specific user.

Wanted to know if this is at all possible with the DB2 time travel and how we can achieve it .


texts: McAfee antivirus and File stream SQL Server 2008 R2 Issue

texts:
I am facing file stream issues in SQL Server 2008 R2 and I am not able to correlate things and how to fix them.
Below are the event logs. Please let me know if they both the errors (One is Mccafee anti virus and other file stream access issue )are linked to each other.

Blocked by access protection rule.  Access to object C:\PROGRAM FILES (X86)\MCAFEE\VIRUSSCAN ENTERPRISE\SHSTAT.EXE was blocked by rule Common Standard Protection:Prevent termination of McAfee processes.
Event ID:      5571
Description:
Internal FILESTREAM error: failed to access the garbage collection table.


texts:
In a database table I have made a date attribute, but I have set its type to varchar and not Date. My question is: will I still be able to compare such dates in a SQL Query?

Dates in my DB are stored in this format:

  dd/mm/yyyy hh:mm:ss

I have to do a SQL Query in PHP that looks something like this:

SELECT *
FROM DBtable
WHERE DBname='$name' AND date>='31/01/2015' AND date<='31/09/2015';

'date' looks like this: 07/08/2015 15:15:02

I would appreciate an example of how to do this.


texts: Convert rows to column for every nth row by ID

texts:
I'm trying to convert rows to columns.

I have the following columns: user_id, meta_key, meta_value

I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'

Right now I can generate the rows and columns sorted and from a specific user_id. In my case it's from 15. This is with the following query :

SELECT user_id, meta_key, meta_value
FROM DATA_A ORDER BY user_id DESC;
where meta_key IN ('first_name', 'last_name', 'stillingsbetegnelse')
AND user_id > 15;

And the result is:

user_id     meta_key                meta_value
16          first_name              Laxgdfds
16          last_name               Narentdsaa
16          stillingsbetegnelse     Andet
18          first_name              Riedsa
18          last_name               Joe
18          stillingsbetegnelse     Andet

(all information is anonymized)

How do I get the following output?

first_name    last_name         stillingsbetegnelse
Laxgdfds      Narentdsaa        Andet
Riedsa        Joe               Andet

I have tried methods with pivot, but I'm getting an error:

    select
        p.*
    from DATA_A
    pivot(
        max([meta_value])
        for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse])
) as p

I think the problem is that I want to take values only from a certain ID. How can I implement this feature? I only want the states data above, but I'm getting a lot of other data, that I don't have any use for.
...........................

With help i found a solution from Stackoverflow.
The solution is

> SELECT user_id,    MAX(IF(meta_key = 'first_name', meta_value,NULL))
> AS Fornavn,    MAX(IF(meta_key = 'last_name', meta_value,NULL)) AS
> Efternavn,    MAX(IF(meta_key = 'stillingsbetegnelse',
> meta_value,NULL)) AS  FROM DATA_A GROUP BY user_id;


texts: How to create an INSERT/UPDATE/DELETE trigger

texts:
My knowledge with triggers is very poor.

I want to create an INSERT/UPDATE/DELETE trigger on a tableA with column names (A1,A2,A3,A4,A5,A6). After the triggers fires, I want to put that data in tableB with at least two different column names (B1, B2, A3, A4, A5, A6).

Basically if  there is a change in tableA, that change should go in to tableB in the respective columns, i.e. A1->B1, A2->B2, A3->A3, like that.

Can anyone please help me with this if possible?


texts: What are the pitfalls when storing data retrieved from an api

texts:
I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the product, what they charges us for shipping, etc.

To report on and analyze this data I want to store it in a table. I have some concerns:

What if i load 1 Jan - 31 Jan today, and then next week they change some data in that range? With thousands of products sold daily combing over that to look for necessary updates could be intense.
How should I manage adding new data? I'm thinking to get new data mid-month for the previous month. This vendor's practices are not the best, I'm concerned they'll add some things one Monday that they forgot to enter the previous Friday. I think if I get today's data every day the likelihood of changed data in a past period rises.

Appreciate any advice from folks who've faced a similar issue. Maybe people do this all the time and I'm worried over nothing. Just would like to get some advice before I proceed.


texts: Sysbench is a tool to analyze the MySQL performance. I downloaded it and extracted but there is no executable file. Files having extensions like .sh or .c or .h are available. I gone through the documentation. Even, I did not get any info about how to install and use this great tool on Windows machine?


texts: Implementation of SSL ON SQL SERVER DEFAULT INSTANCE

texts:
We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS.

We imported the same certificate on the SQL Server machine.

However, when trying to configure the instance from configuration manager there is certificate showing up in certificate tab.

With reference to link for deploying and troubleshooting certificate: http://support.microsoft.com/kb/316898/

Apparently the certificate was not issued to the machine FQDN/hostname as there is no domain for the SQL Server machine or the Web Server machine.

I tried creating an OpenSSL certificate on a machine using its hostname but when trying SCM to configure the instance after certification deployment there's no certificate in the tab.

Questions:

Does an OpenSSL certificate or a purchased certificate use port 443?  Reference this link: https://stackoverflow.com/questions/9342769/sql-server-cannot-find-certificate.
If yes, what is the GUID for my SQL Server instance or where can I get this info while running "netsh".
Above link also states.

  “The last step was making sure the account running SQL Server had permission to read the certificate. I went into the certificate snap-in and then went to properties under the certificate, then on the Security tab I gave the Network Services account read permission on the certificate. (NOTE: I did not find any tutorials online reference this step. I was just some dumb luck that I found this.”

I couldn't find any security tab; is there something other than the MMC wizard to see this property of certificate?

Server details:

Window Server 2008 R2
SQL Server 2008 Web Edition

What am I missing?


texts: SQL Server AAGs/Possible Endpoint contention/Synchronous replication to WAN Secondary

texts:
In the interest of saving some SQL Server licensing moolah ($$$), we’re going down the path of extreme consolidation. We’re trying to shoehorn as many databases as possible into a SQL Server 2016 Always On Availability Group “Cluster” which, the way I see it is really one active server at a time with the data replicated to other standby secondaries. To me, the word “cluster” (as Microsoft SQL Server does it) is more of a marketing term and a dangerous one at that because it gives the impression of a big bad server when it is not.

So on this new environment we have, among other things, a default instance of SQL Server hosting about 20 AAGs, and a second instance to host Sharepoint as well. Overall a total of potentially 70 databases in these AAGs (including classic hits like MS SCCM which uses service broker and is highly transactional+did I mention, Sharepoint) on a 4 core SQL Server VM w/72GB of RAM. One of the things I noticed is that each AAG communicates with a single HADR endpoint on the destination instance so you end up with 20 AAGs communicating with a SINGLE endpoint.

Will all of those AAGs sending log data to a single endpoint (single tcpip port) create a point of contention?

The design was to use synchronous replication EVEN to the remote site which I immediately saw as a potential problem but I was told that there have been significant improvements in SQL Server 2016 specific to AAG replication. Your thoughts here will be appreciated as well.

Will all of this work? Is it fair for me to wear a Nostradamus hat and predict a potential Cluster-Mesa or should I play “Let it Be” like the Beatles and keep on trucking...


texts:
We are using oracle 11g

I am replicating production db for dev environment.
I Have a user named CST2 and CST0 where CST0 contains all tables and view and public synonyms for the same. I have provided same set of privileges for CST2 in dev env as in production.

There is a view named Product(based on product_info and product_ainfo) in CST0 which can be accessed by CST2 in production(Did select). but doing the same in replicated dev env is giving the error insufficient privilege.

I checked following means from which the user can get privileges.

the roles assigned are same and privileges for that roles are also same.
usr_tab_privs are same
usr_sys_priv are same

I cant find out how the CST2 in prod is able to access view 'product' in CST0 as it got no privilege through roles or tab_privs.

Am I missing any other way by which CST2 in prod got privilege which I do grant in dev.?


texts: Timestamp columns in Peer to Peer Replication

texts:
According to MSDN post https://msdn.microsoft.com/en-us/library/ms151196(v=sql.105).aspx Timestamp columnns are restricted in Peer to Peer replication.
Does this mean that we can't use Timestamp columns in database that we are going to replicate ?


texts: Store different rows type in single table

texts:
I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type (Transition, Position, Error):

Is this a good approach? Possible drawbacks:

as a result I'll have to take care that every row in Event exactly 1 FK not null as it can relate to exactly 1 event
when adding new event type I'll have to upgrade* Event
when I'll have many event types the result will be that a row in Event table will have mostly null columns and only 1 will be not null

EDIT: Transition, Position, Error, Location tables has unique columns which are not put in the image above, e.g. Transition has type, Position has 'latitude'/'longitude', Error has payload etc.

* By upgrade, I mean when I need to add new type to Event table I'll require rewrite whole Event table - as I'm working on Android it'll require using method SQLiteOpenHelper->onUpgrade.


texts: Count values to show up with zeros instead of null

texts:
I'm trying to make a query where I'll have the id and the number of employees that person is their superior, also people that aren't managers, should appear and have a count of zero.

This is what I did but those that aren't managers show up with a count of null:

create view scnt as (
    select E1.super, count(distinct E1.id) cnt
    from Employee E1 left outer join Employee E2
    on E1.super = E2.id
    group by E1.super
);

create view numSuper as (
    select E.id, S.cnt
    from Employee E left outer join scnt S
    on S.super = E.id or E.id = null
);

select * from numSuper

Here
That's the schema:

but we can ignore all the noise and use just what we need: employee(id, super)

Here's a fiddle with the inserts: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=4c08869d0e8eba6c82fd4957092379a1

Any ideas on how to do this?


texts: Why does CONTAINS not find matches at the end of the string?

texts:
I have a stored procedure in Microsoft SQL Server 2008 R2 (SP3) which searches a table for anything matching a @Keywords parameter that I pass in. It was too slow using LIKE as the table has 155,000 rows, so I created a fulltext catalog and switched to using CONTAINS instead. I am not very familiar with this feature, but after researching it seemed all I needed to do was process the user's choice of words with:

SET @Keywords = '"' + @Keywords + '*"'

Adding the double quotes and asterisk worked well until someone discovered they had no results when searching for a string they knew definitely existed. It seems that the * is stopping CONTAINS from finding rows where the @Keywords are right at the very end of the string.

Analysis with very simple t-SQL:

--first I prove that I can find the one row which the user was searching for
--this column is a NVARCHAR(200)
SELECT * FROM News
WHERE Headline = '1120 days at sea and still sailing' --1 row returned (OK)
SELECT * FROM News
WHERE Headline LIKE '1120 days at sea and still sailing%' --1 row returned (OK)

--using the full headline
SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing*"') --no rows, just like my proc (WRONG)
SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing"') --1 row returned if asterisk omitted (OK)

--using part of the headline so we don't touch the end of the
SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still"') --lots of rows, since it is anything with 'days' and then 'sea' present (NOT USEFUL)
SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still*"') --no rows (WRONG)

I note that CONTAINS considers at/and/still to be 'noise' words and ignores them. Fair enough. The word sailing is the troublesome one at the end of the string.

My question:

How should I modify the incoming keywords to make sure than CONTAINS can find results regardless of the position of the string of keywords within the column value?


texts: How to get MSSQL Type definition from system tables?

texts:
If I have a user table type (like CREATE TYPE ...), how do I get to it's definition in the system tables?

I know I can see it through SSMS through GUI, but I would like to have a query to retrieve it from system tables.

I don't see it through any of the modules:

SELECT
      *
FROM
      sys.all_sql_modules m
WHERE
      m.definition like 'CREATE TYPE %' --doesn't output any types definitions