texts: Help needed to tidy up deprecated (and mal-functioning) SQL code

texts:
I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4).

I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (seems to be picking an outdated instance of data: my hunch here is that some of the many joins are buggy).

First off, I'm therefore trying to clean it up and then see why "wrong" results are (sometimes) produced.

My first ideas of what needs cleaning up is this (please feel free to add further ideas to the list :):

the Oracle (+) Operator, by re-writing it to LEFT OUTER JOIN's
the mix of "old, ancient and fragile implicit joins" by re-writing them
as explicit JOIN's

Here goes an attempt at producing an equivalent of an MWE (minimal working example), though I don't have online data to share here by which this thing could actually fly. This is an attempt to take the main elements of the inherited code and syntax:

SELECT
    *
FROM
    a1,
    a2,
    a3,
  ( select * from b3 where col4 in
        (select col5 from b6 where co7 = '007')
  ) c,
    a7
WHERE
      a1.col8 = c.col9 (+)
AND
      c.col4 = a2.col5 (+)
AND
      a2.bla = a3.bla
AND
      a2.id = a7.id (+)

Here's the code above with the schemas present in the FROM section (if that's of any help?). In the WHERE section, the aliases are used already and no schema seems to be referenced...

Oracle seems to have a specific understanding of what a schema actually is... Below, SCHEMA_A would a name in the tree-branch-list under "Other Users" and a1 would be a table beneath that (on a twig of said branch).

SELECT
    *
FROM
    SCHEMA_A.a1,
    SCHEMA_A.a2,
    SCHEMA_A.a3,
  ( select * from SCHEMA_B.b3 where col4 in
        (select col5 from b6 where co7 = '007')
  ) c,
    SCHEMA_A.a7
WHERE (...)

Related links:

https://stackoverflow.com/questions/23851976/left-outer-join-with-subquery-syntax
https://stackoverflow.com/questions/49549506/left-join-on-multiple-tables-error-table-or-view-does-not-exist
https://stackoverflow.com/questions/4020786/oracle-operator
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 (Oracle recommending to not use the Oracle (+) operator, for several reasons)


texts: Restoring Postgresql database on Windows if only "data" folder available?

texts: Is is possible to restore a PostgreSQL database if all I have available is the "data" (C:\PostgreSQL\data in my case) folder of the server whose databases I want to restore.I know this folder contains all the database files but I have no idea how to go about restoring the databases in this scenario. I'm using PostgreSQL 10 and Win 2008 R2.


texts: "Corrupted key" error when creating/updating SQL Server 2012 credential for proxy account

texts:
Following a restart of a SQL Server 2012 SP3 instance caused by a reboot of the unresponsive host WINTEL server, I have a SQL Agent Log Shipping Copy job (which uses a proxy account to access the NAS file-share where the log backups reside) that is failing with this error:

  Error authenticating proxy MyDomain\MyProxyAccount, system error: The user name or password is incorrect.

I have attempted to reapply the correct password to the proxy’s Credential and to recreate a new Credential and both fail with this error:

  A key required by this operation appears to be corrupted. (Microsoft SQL Server, Error: 15507)

I’ve read an MSDN page that says:

  SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. The Service Master Key is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key.

I’m uncertain how to proceed. Do I have a problem with the Service Master Key? If so, can anyone suggest how to resolve this issue or direct me to further reading online?

Update: I've found entries in the SQL Server log (written when the instance was restarted following the host WINTEL server reboot) that further suggest that the issue lies with the Service Master Key:

  Service Master Key could not be decrypted using one of its encryptions.
  An error occurred during Service Master Key initialization.

I'd still welcome any suggestions on how to fix this...


texts: When do I need to install Oracle database client?

texts: I know Oracle database client provides basic network components and in order to connect to an Oracle database, I have to install it on the client machine. I noticed for Oracle's SQL Developer, I haven't installed any client and it works quite well and can connect to the database without any issues. For Toad, it seems I need to install a client to make it work. My question is, when should I install the  Oracle client? How do I know 32bit or 64bit version is needed?


texts:
Backup goes through if I don’t have any explicit connection to any instance :

RUN {
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH00_%U';
ALLOCATE CHANNEL ch2 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH01_%U';
ALLOCATE CHANNEL ch3 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH02_%U';
ALLOCATE CHANNEL ch4 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH03_%U';
Backup incremental level 0 database;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
}

But it fails when I specify explicitly the instance name :

RMAN> RUN {
2> ALLOCATE CHANNEL ch00 TYPE DISK connect 'sys/oracle@realdb_1' FORMAT '\\192.168.2.11\test\DF_CH00_%U';
3> ALLOCATE CHANNEL ch01 TYPE DISK connect 'sys/oracle@realdb_2' FORMAT '\\192.168.2.11\test\DF_CH01_%U';
4> ALLOCATE CHANNEL ch02 TYPE DISK connect 'sys/oracle@realdb_3' FORMAT '\\192.168.2.11\test\DF_CH02_%U';
5> ALLOCATE CHANNEL ch03 TYPE DISK connect 'sys/oracle@realdb_4' FORMAT '\\192.168.2.11\test\DF_CH03_%U';
6> Backup incremental level 0 database;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel ch00
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12154: TNS:could not resolve the connect identifier specified

Any ideas ?


texts:
I get the following error message when trying to restore the database from backup using RMAN.

rman target /

RMAN> set dbid=130474031
RMAN> configure device type 'sbt_tape' clear;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> restore database;

Starting restore at 22-DEC-15
using channel ORA_DISK_1

creating datafile fno=1 name=/opt/oracle/oradata/prod/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/22/2015 12:47:01
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'
RMAN>


texts: MySQL Slave replication: Need to determine where replication left off to start slave in correct location

texts:
Idiotically I rebooted a machine that is used as a MySQL slave without running STOP SLAVE, FLUSH TABLES first.

I had thought MySQL automatically handles all of this during a machine reboot, but apparently it doesn't, at least not in the configuration I was using, because the SLAVE doesn't start back up.

mysqld does start but there is an error message in the logs indicating the slave portion stops due to duplicate primary key issues.  Meaning it is trying to insert data that has already been added.

Here are the errors generated in the mysql log:

120104 11:07:54 [Warning] Slave: Duplicate entry '94459' for key 'PRIMARY' Error_code: 1062
120104 11:07:54 [ERROR] Error running query, slave SQL thread aborted.
    Fix the problem, and restart the slave SQL thread with "SLAVE START".
    We stopped at log 'mysql1-bin.000362' position 3384732

How do I determine where in the master binary log the process needs to start from to run a CHANGE MASTER statement?  I know I could potentially, skip log entries using the sql_slave_skip_counter but without knowing how many to skip I would need to go one by one, and that could take all day.


texts: MySQL running in safe mode but not in normal mode. Can't find file: './mysql/plugin.frm' (errno: 13)

texts:
My server accidentally got hard rebooted yesterday and after that the MySQL service isn't starting.

On running sudo service mysql status

the response was stuck at mysql stop/waiting

On running mysqld --verbose this is the response I got

200305 12:47:59 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

200305 12:47:59 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.

200305 12:47:59 [Note] mysqld (mysqld 5.5.47-0ubuntu0.14.04.1-log) starting as process 2536 ...

200305 12:47:59 [Warning] Can't create test file /var/lib/mysql/ip-172-31-12-215.lower-test

200305 12:47:59 [Warning] Can't create test file /var/lib/mysql/ip-172-31-12-215.lower-test

200305 12:47:59 [Warning] One can only use the --user switch if running as root

200305 12:47:59 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.

200305 12:47:59 [Note] Plugin 'FEDERATED' is disabled.

mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

200305 12:47:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

200305 12:47:59 InnoDB: The InnoDB memory heap is disabled

200305 12:47:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins

200305 12:47:59 InnoDB: Compressed tables use zlib 1.2.8

200305 12:47:59 InnoDB: Using Linux native AIO

200305 12:47:59 InnoDB: Initializing buffer pool, size = 1.0G

200305 12:47:59 InnoDB: Completed initialization of buffer pool

200305 12:47:59  InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

InnoDB: File name ./ibdata1

InnoDB: File operation call: 'open'.

InnoDB: Cannot continue operation.

Currently I can run mysql in safe mode but I'm not sure if there are any issue's in doing that and since this is in production I can't play around with different solutions which would lead to a longer downtime.

TIA


texts:
I'm working on a "Bus stations" database. I want to design the next relationship:
"A bus line has many stops (one stop belongs to one line) and one stop has many arrival times (one arrival time belongs to one stop)".

So my design is like this:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times

Lines(id, name, ...)
Stops(id, line_id, name, ...)
Times(id, stop_id, ...)

But when I was testing it, I realized that I needed a FOREIGN KEY line_id in Times table (because I could not distinguish between times of different bus lines). This transforms the diagram:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times
  |                                       |
  |                                       |
   ---------------------------------------
  (1,1)                                  (1,N)

I'm a little bit confused because I think this design has redundancy: the relationship between Lines and Times could not be reached by the intermediate relationships?


texts:
I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space).

Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need to know the safest way of copying the databases over to a new server.

All advice appreciated!


texts: I am designing a database for a game and the only option I can see a multiple table solution. Is there an option that I'm not seeing?

texts:
I searched but could find a similar situation, if I missed it please let me know. In this game there are approx 14,000 items which have their own table. The players will want to build multiple collections of these items. The collections would generally range from 20 to 100 different items in varying quantities, but could have hundreds of different items. The problem comes with the storage of these collections. I can't create a collections tables with each row a collection because the number of fields varies so much.
I only see two solutions:

Create a collections table with a BLOB data field and store the data as a JSON object.
Give each collection its own table with an item ID field and a quantity field. Create a collection list table with the user id and the corresponding table name.

Is there a better option? If not, I am leaning toward option two so I can easily run statistic queries. Due to the number of tables that could exist would it benefit me to keep the collections in a database separate from my other tables?


texts: SQL Server 2014 Not recognizing 64 bit drivers for Office

texts:
I have a SQL Server 2014 virtual machine. It is a 64 bit version of SQL Server or a 64 bit Windows Server. The SQL Server software was installed before I was hired by the Sysadmin. He choose to have the 32 bit drivers for Access (Office) installed. We’ve talked about it, he claims he did it because we are using a 32 bit version of Office 2010.

Anyway, I uninstalled the AccessRuntime_x32 and installed AccessRuntime_x64. I’ve downloaded both the 2010 and 2013 versions off the Microsoft Website. I’ve restarted SQL Server, I’ve even rebooted the whole server. Yet I still can’t export out more than 64,000 rows of data for Excel nor can I choose a new version of Microsoft Excel past 2007. What am I missing?


texts: Oracle how to manage the flashback log space?

texts:
I set db_flashback_retention_target=1440min,Why the flashback log kept 7 days? Thanks!

closed as unclear what you're asking by Phil, Michael Green, Tom V, Shanky, Andriy M Jan 5 at 12:53

Why the sql-server users can close my question?
They know what flashback_log is?


texts:
Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. Prominent example: expression indexes only allow IMMUTABLE functions and those have to be truly immutable to avoid incorrect results.

User-defined functions are still free to be declared as the owner chooses. The manual advises:

  For best optimization results, you should label your functions with the strictest volatility category that is valid for them.

... and adds an extensive list of things that can go wrong with an incorrect volatility label.

Still, there are cases where faking immutability makes sense. Mostly when you know the function is, in fact, immutable within your scope. Example:

Does PostgreSQL support “accent insensitive” collations?

All possible implications on data integrity aside, what is the effect on performance? One might assume declaring a function IMMUTABLE can only be beneficial to performance . Is that so?

Can declaring function volatility IMMUTABLE harm performance?

Let's assume current Postgres 10 to narrow it down, but all recent versions are of interest.


texts: First-In and Last-Out times for Night Shift and Day Shift Employees

texts:
Kindly share suitable technique to handle night shift as well as day shift.
Below are the shifts,
Night,  2020-07-20 17:00 to 2020-07-21 02:00
Night,  2020-07-20 17:00 to 2020-07-21 02:00
Day,    2020-07-20 08:00 to 2020-07-20 17:00
Day,    2020-07-20 08:30 to 2020-07-20 17:30

In Database there are only two fields
1: SSN (which is employee Code)
2: CheckTime e.x. (2020-07-20 16:48:30.000)

in Day shift i can get first in last out from Min entry and Max entry.
but in night shift I can't get any accurate data because of the date change.


texts: Choice- or ids-list- field representation

texts:
I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to Many relation).

I would like to know what is commonly used and what are the drawbacks/assets of each solution (in terms of performances, integrity and sustainability).


texts: Find source of spids that show no hostname

texts:
I have a busy SQL 2012 server where connections to one particular database (using the same SQL login) don't show a "hostname", and I need to find out which box (of several) these connections are coming from.

I've tried all the normal built-in and 3rd-party tools (master.sys.processes, sys.dm_exec_sessions, sp_who2, sp_WhoIsActive, the SSMS Activity Monitor, even SQL Profiler and Idera Diagnostic Manager), none of them can give me the hostname of the server/client behind these connections, presumably because they all use the same underlying system tables/views.

I'd be fine with an IP address, too, but don't see any way to determine that from within SQL either.

In case this is any help:

sys.dm_exec_sessions shows the client_interface_name is ODBC
Nothing captured for program_name either
The user is a SQL account that happens to be a SYSADMIN (yes, I know...)

I found a related question here (albeit from the other side), but that went unanswered.

At this point I'm tempted to disable the user so I can capture some failed logins in the SQL errorlog, but I don't imagine the application team will be happy with that.


texts:
I want to display the values that are between 0 and 10 and dont exist in the current table

For example :

Field
1
2
3
5
9
8

Result expected:

7
4

The problem is that the column field is a varchar, so i have first convert to number and then make the validations

SELECT TO_NUMBER(yy.ent_sub_dealer_code,'9') FROM a_entidades yy
where TO_NUMBER(yy.ent_sub_dealer_code,'9') not in (SELECT LEVEL n
                                                   FROM DUAL
                                                   CONNECT BY LEVEL <= 9     )

Any suggestions?


texts:
I am looking for a query
to find nth value in a list.  The separator is anything greater than or equal to 2 spaces. (it can be 3, or 5 spaces).
Trying to avoid scalar value functions, since performance may be slower. The sentences can have any number of words, from 5-20.

CREATE TABLE dbo.TestWrite (TestWriteId int primary key identity(1,1),
                            TextRow varchar(255))
INSERT INTO dbo.TestWrite (TextRow)
SELECT 'I am writing SQL Code.'
UNION ALL
SELECT 'SQL keywords include join, except, where.'

+-----+----------+---------+---------------+---------+----------+
| SQL | keywords | include |     join,     | except, |   where. |
+-----+----------+---------+---------------+---------+----------+
| I   | am       | writing |    SQL  Code. |         |          |
+-----+----------+---------+---------------+---------+----------+

Would like in individual rows with columns, see comments above.

This may be one solution trying to utilize.
https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')


texts: Allow multiple users to change functions in MySQL

texts:
I want to make multiple users and allow them to change  procedures,functions and triggers in same DB. There must be user devel for    developers and admin for admin, both can change developers DB, but admin is not root, i.e. he can't change everything, only some databases.
User admin have following permissions:

GRANT ALL PRIVILEGES ON some_db_prefix%.* TO 'admin'@'%' WITH GRANT OPTION

All fine except definers in triggers and procedures. If I set definer in database to devel@'%', i can't edit procedures as admin, but root user can, of course.
How can I allow to modify triggers,functions and procedures to multiple users?

UPD: I can't edit procedures as admin even when definer is `%`@`%`.

UPD2: MySQL version:
Server version: 5.6.23-ndb-7.4.5-cluster-gpl-log MySQL Cluster Community Server (GPL)

UPD 3: (moved to new question Allow multiple users to see procedures and functions )

UPD 4: Manuals say that only way to change body of procedure is to drop it and create new. So, I can do it, and my question is incorrect, I want not only change functions/procedures using different users, but also see body (code) of funtions/procedures. That's why I have created new question.


texts: Key column 'FK_UserQuestion' doesn't exist in table error

texts:
I used the following syntax and keep getting the following error at the end. " Key column 'FK_UserQuestion' doesn't exist in table":

CREATE DATABASE QA;

CREATE TABLE QA.Users
(
UserID int AUTO_INCREMENT PRIMARY KEY,
fName varchar(150),
lname varchar(150),
isadmin bit
) engine = INNODB;

CREATE TABLE QA.Question
(
QuestionID int AUTO_INCREMENT PRIMARY KEY,
UserID int,
Question varchar(65535),
FOREIGN KEY (FK_UserQuestion, UserID)
    REFERENCES QA.Users (UserID)
) engine = INNODB;

CREATE TABLE QA.Answer
(
AnswerID int AUTO_INCREMENT PRIMARY KEY,
QuestionID int,
UserID int,
Answer BLOB(65535),
IsSolver bit,
FOREIGN KEY (User_ID, UserID)
    REFERENCES QA.Users(UserID)
FOREIGN KEY ( FK_AnswerQuestion,QuestionID)
    REFERENCES QA.Question (QuestionID)
) engine = INNODB;

I've tried to put out the "QA." from each table, and tried to run statements that are separated by semi-colon, each turn too, but nothing solved. Tried many things and nothing I did is correct for MySQL Workbench 6.3 CE.


texts:
i created the following schema , inside that schema i created a table and inserted a some values.

After that i created a function that get a value in the table based on an ID.

Here is all the declarations :

CREATE SCHEMA pckg_50d_facture;
CREATE TABLE pckg_50d_facture.valeur(val_id text PRIMARY KEY, val text);

CREATE OR REPLACE FUNCTION pckg_50d_facture.const(id text) RETURNS text AS $body$
DECLARE
result text;
BEGIN
  --EXECUTE 'SELECT val FROM pckg_50d_facture.valeur WHERE val_id = id' INTO result USING checked_user, checked_date;

    SELECT val INTO result FROM pckg_50d_facture.valeur WHERE val_id = id;

 return result;
END;
$body$
LANGUAGE PLPGSQL
;

But when i call the function const i get the following error :

ERROR:  relation "valeur" does not exist

I tried to Grant some rights as follow :

GRANT USAGE ON SCHEMA pckg_50d_facture TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA pckg_50D_facture TO PUBLIC;

but still not working.


texts: Split data into X days' Cohort (SQL Server)

texts:
Hi I want to split data by x days' cohort.

the table

SELECT
 --earliest_time,
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day >  0 and by_day <= 10 then 1 else 0 end) day_10,
    sum(case when by_day > 10 and by_day <= 20 then 1 else 0 end) day_20,
    sum(case when by_day > 20 and by_day <= 30 then 1 else 0 end) day_30,
    sum(case when by_day > 30 and by_day <= 40 then 1 else 0 end) day_40,
    sum(case when by_day > 40 and by_day <= 50 then 1 else 0 end) day_50,
    sum(case when by_day > 50 and by_day <=60 then 1 else 0 end) day_60,
    sum(case when by_day > 60 then 1 else 0 end) day_60plus
from cohort_days co

This splits the data into cohorts of 10 days. If I want to change it to 20 days' cohort, then I need to edit them 1 by 1. I want to just define x days' cohort once, then the data can generate the cohort groups itself automatically.

I want to know is there easier way to do this than the above code? For example, I just need to define x days' cohort once at the beginning then I can use loops to iterate over it to generate the rest of the cohort groups.

A bit like this:

declare @d int
select @d = 20
select * from cohort_days
SELECT
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day > 0 and by_day <= @d then 1 else 0 end) CONCAT('ay_: ', @d),
    select @2d = @d*2
    sum(case when by_day > @d and by_day <= @2d then 1 else 0 end) CONCAT('ay_: ', @2d)
    select @3d = @d*3
    sum(case when by_day > @2d and by_day <= @3d then 1 else 0 end) CONCAT('ay_: ', @3d)
from cohort_days co

If I want to change cohort days to 50 days, I just need to change select @d = 20 to select @d = 50


texts:
I understand the concept of pre-aggregating data so that multi dimensional queries perform faster, but the descriptions I've seen of aggregations in SSAS seem to imply that an aggregation only involves a single dimension or attribute.  For example the example in this article shows an aggregation for just time:

http://msdn.microsoft.com/en-us/library/ms174587.aspx

How does SSAS leverage such an aggregation when you query with attributes from two different dimensions such as route and time?  It would have to somehow take the aggregation for 2nd Quarter packages of 6005 and break it up into sea, air, etc. which I don't see how it would be able to do that without simply going back to the leaf level data.

The only other possibility I could imagine is that it has pre-aggregations which store many possible combinations of dimensions.

Since you can't actually view the pre-aggregated data, to see what its little wizard comes up with, it is difficult for me to visualize how this works. (I'm the kind of person who has to know the low level details before I really feel like I understand something and can use it properly).

How are aggregations structured, and how is SSAS able to leverage a pre-aggregation for queries which query multiple dimensions?


texts: How do i disable or limit Postgres Replication creating causing too many WAL files generated?

texts:
On AWS RDS PostgreSQL 10.7,
I turned on logical replication for a one specific table
.

on publisher:

CREATE PUBLICATION mypublication FOR TABLE <mytable>;

on subscriber:

CREATE SUBSCRIPTION mysub
         CONNECTION 'host=... port=... user=foo dbname=foodb'
        PUBLICATION mypublication;

However, this led to some interesting errors where the WAL files generated ate up > 100 times the size of the table I replicated and I had to shut it down.
I started following the AWS guiding for troubleshooting this: https://aws.amazon.com/premiumsupport/knowledge-center/diskfull-error-rds-postgresql/

on AWS RDS, this was indicated by TransactionLogsDiskUsage metric.

It seems almost like it's trying to generate WAL files for any changes to the DB instead of just that table.
This was indicated by OldestReplicationSlotLag increasing when there are write to any tables instead of the specified table.

I tried looking at postgres error logs on RDS but still can't really understand the cause. Is there way to limit the WAL files created by logical replication?


texts: Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

texts:
I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get aggregated through a process of copy-paste and buggy macros.

My intent is to create a simple python script which aggregates all of the data for an experiment into an SQLite database and then produces the requisite CSV/XLSX output.

My issue is that for a single trial of our experiment, we end up with about 100 variables recorded at about 10 different time points. My initial impulse was to create a value and variable table:

CREATE TABLE value (val_id INTEGER PRIMARY KEY,
                    value TEXT,
                    var_id INTEGER,
                    event_id INTEGER,
                    exp_id INTEGER,
                    FOREIGN KEY (var_id) REFERENCES variable(var_id),
                    FOREIGN KEY (event_id) REFERENCES event(event_id),
                    FOREIGN KEY (exp_id) REFERENCES experiemnt(exp_id)
);

CREATE TABLE variable (var_id INTEGER PRIMARY KEY,
                      var_name TEXT,
                       var_type TEXT
);

value:
val_id | value | var_id | ...
0      | 10    | 0
1      | "ROSC"| 5

variable:
var_id | var_name | var_type
0      | Pressure | DECIMAL
...
5      | Outcome  | TEXT

But this feels wrong and I have a hunch at the "proper" way to do this is to have a single data table with the hundreds of columns that would otherwise be described in the variable table since this would make it easier to do type checking (yes, I know SQLite doesn't do this, but in principle).

Any insight into how to tackle this would be hugely appreciated.


texts: Transaction over linked server causing time out

texts:
When I execute this query on [RemoteServer] linked server, it works just fine:

INSERT INTO [RemoteServer].[DbName].[SchemaName].[TableName] VALUES (1, 'bar');

but when puting it inside a Transaction it doesn't works anymore:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO [RemoteServer].[DbName].[SchemaName].[TableName] VALUES (1, 'bar');
    COMMIT TRANSACTION
    PRINT 'insert done!';
END TRY
BEGIN CATCH
    PRINT 'insert faild!';
    PRINT 'Error Message: ' + ERROR_MESSAGE();
    ROLLBACK TRANSACTION
END CATCH

RPC and RPC OUT are true over linked server.

When I execute secound script on my client, after timeout shows following message:

OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "No transaction is active.".

(0 row(s) affected)
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.


texts: How do I implement a database/table as a stack

texts:
I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I don't have a way to retain the data structure between incoming web-requests.

I was wondering what would be a good way to implement the stack functionality using databases ?

I need to support:

push(fileName, user) : push a fileName for the user
pop(user) : Pop the top-most fileName for the user

EDIT:

I am prototyping an idea, and so I am using sqlite3 with python.

Thanks!


texts:
I manage a database in SQL Server for a business's customer transaction.  The data is updated four times daily from a very complex Oracle database.  The SQL Server database is then connected to by a .Net application that provides visualization of the data.  This application only reads the SQL Server data, there is no writing back to the database.

At this point the data updates are taking 15 to 20 minutes.  This locks the application out of the database.  I am looking for a way to update the data without overly inconveniencing the users.

I was wondering about doing the updates into a second database, then swapping which database the application talks to.  This of course has to be done entirely on the server side as I don't want to have to make the users log back in.

If I do the updates into a second database, if the DB engine is hitting hard at the updated database, does this greatly affect the performance of users looking at the live database.

I would appreciate suggestions as to how best do this (or some other) approach to minimize downtime for the users.


texts: MySQL sort data before using GROUP_CONCAT to concat it together?

texts:
I have a system that tracks several thousand objects in realtime and stores their locations in a database table.  Rather than store their location every few seconds I simply store their location, direction, speed and time whenever it changes.  This keeps the table relatively small for the number of objects that I'm tracking.

Each row contains data on a single object at a moment in time and whilst these should go into the database in chronological I want to ensure that I pull the data out in chronological order before concat'ing them together so that I can plot out the data.

My table structure is:

CREATE TABLE tracks (
    id int(11) auto_increment,
    objectId int(11) default 0,
    posTime bigint(15) default 0,
    X float(6,4) default 0.0000,
    Y float(6,4) default 0.0000
    Z int(11) default 0,
    hdg float(4,1) default 0.0,
    spd float(5,1) default 0,
    PRIMARY KEY(id));

By using this query:

SELECT   objectId,
         group_concat(X, ',', Y, ',', hdg, ',', spd separator ',') as trail
FROM     tracks
WHERE    objectId > 0
group by objectId;

I get the data that I want in the format that I want e.g.:

14979491 | -26.3286,-48.4679,206.0,362.0,-26.3684,-48.4892,205.0,357.0

i.e. ObjectId | X1, Y1, heading1, speed1, X2, Y2, heading2, speed2, .... etc

However I'm not 100% confident that the position reports are in the correct time order.

How can I sort the position reports for each id by increasing posTime before concat'ing them together ?  In theory it's just a case of adding ORDER BY posTime ASC but I'm not sure how or where to add this to my query as all previous attempts have failed ...

EDITED TO CLARIFY: I know that group_concat supports an ORDER BY clause but I don't want the column that I sort by (posTime) to be returned in the results.


texts:
I'm trying to enable MySQL Query Cache on Ubuntu 15.04 with MySQL 5.6.25

I've added this to end end of /etc/mysql/my.cnf and /etc/mysql/conf.d/mysql.cnf:

query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

The whole server has been restarted more than once.

user@myhost:/$ mysql
mysql: unknown variable 'query_cache_type=1'

Using SHOW VARIABLES LIKE '%query_cache%' confirms that query_cache_type = OFF

SET GLOBAL query_cache_type = 1;
/* SQL Error (1651): Query cache is disabled; restart the server with query_cache_type=1 to enable it */

How can I solve this?


texts: How to get best sellers per headquarters

texts:
Hi i need to get the best seller of all products per headquarters:

I have this query:

SELECT Employees.first_name, Employees.last_name
     , Sold_products.product_type, Headquarters.country
     , Headquarters.city, Headquarters.street
     , COUNT(*) AS TOTAL
FROM Sold_products
   , Employees
   , Headquarters
WHERE Employees.id = Sold_products.employer_id
  AND Employees.headquarters_id = Headquarters.id
GROUP BY Employees.first_name, Employees.last_name
       , Sold_products.product_type, Headquarters.country
       , Headquarters.city, Headquarters.street
ORDER BY Headquarters.country DESC, TOTAL DESC

I want to get result like: | first_name | last_name | Amount of all sold products | country | city | street

And Group it per unique Headquarter - Headquarter in db can have same city and country but not street - streets are unique (e.g. I have many Headquartes in German, Berlin but each have unique street)


texts:
I am using SQL Server 2008 Express edition and I've worked two days on a problem where seems to be a cross join in running a query.

I would want to know the set returned per join operation visually.

Is there any tool that can give you intermediate query results? I couldn't find one on Google.

EDIT

I solved this problem with the oldest trick in the book, manually running the query before joining to see where exactly the problem of cross join was happening. And found out that there was a mapping DB that sent 3 results for a join instead of just one (because it needed two conditions for a proper inner join, instead of one).


texts: Need help in optimizing a slow Postgres Query

texts:
So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number of distinct values of users who occur in the activity table in past 30 days from that day should be calculated.

The query that i have written is returning the desired output. But is too slow. Infact doesn't work for 100 days.

Need Help with optimization !!

Actual Postgresql Code #

Select A.Daily,count(distinct da.user) as Users
from
    (
        select
            date(d) as Daily
        from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 day') d
        group by 1
        order by 1
    ) as A
join public.downloads_activity da
on date_trunc('day',da.created_at) between A.Daily - Interval '30' DAY and A.Daily - Interval '1' DAY
group by 1
order by 1

Expected and Returned Result

      Daily                  Users
January 12, 2020, 12:00 AM    35,654
January 13, 2020, 12:00 AM    35,465
January 14, 2020, 12:00 AM    35,777
January 15, 2020, 12:00 AM    35,980
January 16, 2020, 12:00 AM    35,953
January 17, 2020, 12:00 AM    35,899
January 18, 2020, 12:00 AM    35,829
January 19, 2020, 12:00 AM    35,627
January 20, 2020, 12:00 AM    35,348
January 21, 2020, 12:00 AM    35,564

Query Plan

GroupAggregate (cost=25.16..70992118.48 rows=200 width=26)
  Group Key: (date(d.d))
  -> Nested Loop (cost=25.16..69869352.15 rows=224552867 width=26)
       Join Filter: ((date_trunc('day'::text, ast_d.created_at) >= ((date(d.d)) - '30 days'::interval day)) AND (date_trunc('day'::text, ast_d.created_at) <= ((date(d.d)) - '1 day'::interval day)))
       -> Sort (cost=25.16..25.66 rows=200 width=8)
            Sort Key: (date(d.d))
            -> HashAggregate (cost=15.02..17.52 rows=200 width=8)
                 Group Key: date(d.d)
                 -> Function Scan on generate_series d (cost=0.02..12.52 rows=1000 width=8)
       -> Materialize (cost=0.00..571429.19 rows=10104879 width=30)
            -> Seq Scan on asset_downloads ast_d (cost=0.00..451827.79 rows=10104879 width=30)

Query Plan With Analyze Buffers

GroupAggregate (cost=25.16..71011891.94 rows=200 width=26) (actual time=24608.996..182045.961 rows=10 loops=1)
  Group Key: (date(d.d))
  Buffers: shared hit=325977 read=24911 dirtied=28, temp read=552011 written=118170
  -> Nested Loop (cost=25.16..69888811.94 rows=224615600 width=26) (actual time=2213.459..93364.594 rows=16514694 loops=1)
       Join Filter: ((date_trunc('day'::text, ast_d.created_at) >= ((date(d.d)) - '30 days'::interval day)) AND (date_trunc('day'::text, ast_d.created_at) <= ((date(d.d)) - '1 day'::interval day)))
       Rows Removed by Join Filter: 84818776
       Buffers: shared hit=325969 read=24911 dirtied=28, temp read=488070 written=54229
       -> Sort (cost=25.16..25.66 rows=200 width=8) (actual time=0.081..0.093 rows=10 loops=1)
            Sort Key: (date(d.d))
            Sort Method: quicksort Memory: 25kB
            Buffers: shared hit=3
              -> HashAggregate (cost=15.02..17.52 rows=200 width=8) (actual time=0.044..0.052 rows=10 loops=1)
                 Group Key: date(d.d)
                 -> Function Scan on generate_series d (cost=0.02..12.52 rows=1000 width=8) (actual time=0.028..0.033 rows=10 loops=1)
       -> Materialize (cost=0.00..571588.53 rows=10107702 width=30) (actual time=0.006..3572.397 rows=10133347 loops=10)
            Buffers: shared hit=325966 read=24911 dirtied=28, temp read=488070 written=54229
            -> Seq Scan on asset_downloads ast_d (cost=0.00..451954.02 rows=10107702 width=30) (actual time=0.016..3797.284 rows=10133347 loops=1)
                 Buffers: shared hit=325966 read=24911 dirtied=28
Planning time: 8.678 ms
Execution time: 182102.426 ms


texts: Concatenation behaves differently on two servers

texts:
I have two servers (not master/slaves of each other), each running MySQL 5.1.71-log.

One server 1 (which happens to be in the US), the following query returns good results:

select distinct(devicemode || ' - ' || model || ' - ' || version) as a from <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.key = 1234 order by devicemode ,model,version;

The results look like:

US - MicroMaxx - 30.80.306.024

When the same query is run on server 2 (which happens to be in the UK), I get different results:

0

If I change the query to be:

select distinct(concat(devicemode, ' - ', model,' - ', version) as a from <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.key = 1234 order by devicemode ,model,version;

the same results are returned on both servers:

US - MicroMaxx - 30.80.306.024

Any idea why there would be a difference?

Thanks

Alan


texts: Find the identity of the client firing a query in SQL Server without using triggers?

texts:
I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different clients logged in via the same user name, one faces the conundrum of tracking which of the 5 fired the query. Other specious solutions that I found include altering the CDC table with following command:

ALTER TABLE cdc.schema_table_CT
ADD HostName nvarchar(50) NULL DEFAULT(HOST_NAME())

However, this returns the host-name of the server on which the query was fired, and not the host-name of the client that fires the query.

Is there a way around this problem? Something that would help to log the host-name or the IP address (or some other kind of unique identity) of the client.
I do not want to use triggers, as it slows down the system, also CDC generates system tables, so having a trigger on that is apparently not possible.


texts:
I have a database installed, that I would like to backup  in mysql.  The problem  is mysqldump fails on exporting the 'maia_mail' table

# mysqldump -u root -p maia > maia.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `maia_mail` at row: 15

It runs for less than 30 seconds and gets error out as above.

The total size of the DB is 1.3GB with the maia_mail table being 1.0GB

In my.cnf I have these set:

[mysqld]
max_allowed_packet      = 1300M
[mysqldump]
max_allowed_packet      = 1300M

Please advise or give some guidance on how to dump the database?


texts: How can we have IIS talk to SQL Server with TLS1.0 disabled?

texts:
Background

We need PCI-DSS compliance, and so must disable TLS1.0.  In my test environment we have SQL Server 2014 SP1 with CU 1 (as per this post) and IIS 8 all on the same box.  I used the Nartac IIS Crypto tool to disable TLS1.0 and rebooted.  That's when my troubles began.

SQL Agent won't start, SSMS can't connect to the server, and the website stops working.  (Fortunately, I can still make a Remote Desktop Connection to my test server.)

Then I read Microsoft's article which says basically SQL Server just has to have TLS1.0.

Question

Can I fix this?  (Maybe I need a separate box in test for SQL Server?)


texts:
I'm implementing a medium scale marketing e-commerce affiliation site, which has following estimates,

Total Size of Data: 5 - 10 GB
Indexes on Data: 1 GB approx (which I wanted to be in memory)
Disk Size (fast I/O): 20-25 GB
Memory: 2 GB
App development: node.js

Working set estimation of Query: Average 1-2 KB, Maximum 20-30 KB of text base article

I'm trying to understand whether MongoDB would be right choice for database or not. Index is going to be fairly downsize of Memory but I have noticed that after querying that MongoDB, it has occupied the memory (size of result set) for caching query. In 8 hours I'm expecting that all queries' depth would cover almost 95% of data, in that scenario how will MongoDB manage limited memory scenario also app instance of node.js running on same server.

Would a MongoDB a right choice for this scenario or I should go for other JSON based no-SQL Databases.


texts: Access table field on creating a trigger postgresql

texts:
How can I access table field on creating a trigger?

CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter


texts: SQL Server Job History Disappears After 5 Days

texts:
I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being retained for only 5 days. There is a cleanup maintenance plan but it's set for 5 week retention.

EXEC msdb.dbo.sp_syspolicy_purge_history is being run daily however, the History Retention Days is set to 0 so my understanding is that it's not doing anything anyway.

There has to be something really simple I'm missing here. Can anyone point me in the right direction?


texts: SQL Server spatial filter with additional conditions performance issue

texts:
I have a table Event that has 30 million rows and a table Location that has 35 million rows in SQL Server. An event can have multiple locations.

I have a spatial index, geometry auto grid (cells per object: 4000), on the Location table. The bounding box of all the locations covers and area of about 500 000 km2.

The performance is fine when only using STContains in the WHERE clause. If I try to filter on a additional column(s) the performance takes a big hit.

The following query takes 171ms:

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Position]) = 1

If I add the filter for Price, the same query takes 10 seconds to run. If I remove the spatial index the query takes 500ms. Depending on the size of the bounding box and the value of price the query takes either like second or less or anywhere between three to thirty seconds. Only filtering on the spatial column gives good performance for all bounding box sizes.

Every article I've found about spatial queries in SQL Server never says anything about additional column(s) filters. They only describes the spatial filter and how to tune performance for the spatial index/query and it this case the spatial filter works fine with different sized bounding boxes. The problem is the additional filter columns I need.

Is it possible to get performance from a query with a spatial filter and with additional column(s) filters?

EDIT

95% of the price values are below 5000. So filtering on a high value will only return around 100 rows. I've tried with a bounding box that is 24000 sq km and contains around 4 million events. So the issue might be with the distribution of the price values. I have a second column Date which has a better distribution of values and filtering on that column is fast as long as we don't get to close the the max date.

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE
    [E].[Date] >= @Date
    AND
    @BoundingBox.STContains([L].[Position]) = 1

Also the geometry have srid 4326 if that makes any difference.


texts: Copy a row from a table to another with the same columns but different order in a trigger

texts:
I have two tables:

Document

Columns: {DocumentColumns}

DocumentVersion

Columns: DocumentVersion_id(autoincrement), {DocumentColumns}

{DocumentColumns} are the same in both tables, but their order can differ.

I need that any change that happens in Document, must be added in DocumentVersion, so I created this trigger :

Create trigger copyDocumentVersionTrigger on dbo.Document after insert,update as
insert into [DocumentVersion] select null as DocumentVersion_id,* from inserted

When I want to execute this request I get :

  Msg 206, Level 16, State 2, Procedure copyDocumentVersionTrigger, Line 1 [Batch Start Line 0]
      Operand type clash: bit is incompatible with uniqueidentifier

Because the third column in Document is a bit, and the third one in DocumentVersion is a unique identifier ( and they are totally different columns, that only means that t-sql doesn't care about the named columns in the select, it only cares about the positions).

{DocumentColumns} can be changed dynamically after the creation of the trigger, so I can't just put explicitly the names.

Is there any simple solution to achieve this?


texts: Recalculate and replace large table without interrupting table reads

texts:
There's a large table in the database containing statistical information. This table must be read frequently (by lookups which return a single row).

This table must be completely recalculated every so often. There's a procedure which does the calculation. Currently it does this into a temporary table, and then when it's done it truncates the real table and inserts the data from the temporary table into it.

The problem is that this truncation and insertion takes a while and interrupts lookups during that time. The question is how to do it without interrupting lookups, or only interrupting lookups very briefly.

I'd be happy to hear all options which achieve the desired behaviour; the solution doesn't necessarily have to involve a temporary table. Sorry if this is an established common problem -- I wasn't sure which terms to search.


texts: Impact of adding a MUL index on a table with 35 Million rows. The table has just 3 columns though

texts:
I have table

sql
show table status like 'testing_table' \G;
********************* 1. row *********************
          Name: testing_table
        Engine: InnoDB
       Version: 20
    Row_format: Compact
          Row : 35299598
 Avg_row_length: 198
 Index_length  : 1256210432
 Data_free     : 1008730112

(...)

I am planning to create MUL index on one of the rows in this table

How will this have an impact on the existing table?

Will this improve the performance of my search?

I am afraid with impact of creating a MUL key on the table. Also will this have impact on future inserts?

SHOW CREATE TABLE testing_table
Create Table: CREATE TABLE `testing_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`myKey` int(10) unsigned NOT NULL,
`tKey`  varchar(64) COLLATE utf8_bin NOT NULL,
`tValue` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `myKey` (`myKey`),
CONSTRAINT `testing_table_ibfk_1` FOREIGN KEY (`myKey`) REFERENCES `testing_table2` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=860324165 DEFAULT CHARSET=utf8 COLLATE=utf8_bin


texts: MySQL 2 Million Rows, longtext and varchar indexes

texts:
I'm trying to find a solution for this, with no success.

I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR.

name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE '%PARAM%' ORDER BY views DESC

name is a varchar

text1 and text2 are longtext (~6000 characters each)

text1 and text2 are indexed FULLTEXT
name is indexed BTREE, views too.

The table is MyISAM, it don't have many updates, but many selects;

The query takes ~12sec to complete.

Sorry if this question arleady exists, but I could not found this.

Thank u


texts: How to draw an entity-relationship diagram with "at least" and "exactly" relationships

texts:
The business rules are:

An order must have at least one product, and
a customer must have created exactly one order.

How can I draw an entity-relationship diagram for this?


texts: How much will using a varchar(15) PK affect my tables performance?

texts:
I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. Sadly, these timestamps are not always guaranteed to be unique. There is also a 2-6 (thus the VARCHAR) character hex ID which, when appended to the timestamp, is unique. I tested this out with ~400k records and just doing a SELECT * on the table takes over 15 seconds.

Before I go completely redesigning my table in some drastic way, I want to be sure that using this PK (as opposed to an auto-incrementing INT) is where my performance hit is. I have never really worked on a table using something other than a regular INT PK (I am a developer, not a DBA).

I am using InnoDB engine and a few FK relations to some small tables. MySQL admin is showing the data length of the table at ~150MB, and the index length at 21MB with 380k rows.

As I said, I'm a developer, not a DBA, but in my current situation I don't really have one I can bring in. I did some Googling but found a pretty wide array of answers that often delved into topics that just raised more questions for me. I'm hoping someone here can give a concise answer or at least point me to some more resources.

EDIT: Changed the column to CHAR(14) and removed one large TEXT column that was somewhat superfluous. This seems to have improved the time a good deal and took the table size down to about 80MB, but I'm still looking for suggestions.


texts: Percona Xtradb Cluster 5.5: node can't join cluster - SST fails with auth problem

texts:
I have 3-nodes PXC 5.5 cluster. Settings on node03 in my.cnf [mysqld] section:

wsrep_cluster_address=gcomm://<ip_node1>,<ip_node2>
wsrep_cluster_name=cluster
wsrep_provider_options="gcache.size=128M"
wsrep_node_address=<ip_node3>
wsrep_node_name=node03
wsrep_slave_threads=16
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:XXXXXXXXXXXXXX

Settings on other 2 nodes are the same. node03 can not join cluster:

160807  5:30:18 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '<ip_node3>' --auth 'XXXXXXXXXXXXXX' --datadir '/var/lib/mysql/data/' --defaults-file '/etc/mysql/my.cnf' --parent '9532': 2 (No such file or directory)

Errors in donor node error.log:

160807  5:13:30 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '<ip_node3>:4444/xt
rabackup_sst' --auth '(null)' --socket '/var/run/mysqld/mysqld.sock' --datadir '/var/lib/mysql/data/' --defaults-file '/et
c/mysql/my.cnf' --gtid 'f964a515-38a3-11e6-8d63-47f60c48dba5:38453358'

Errors in donor node /var/lib/mysql/data/innobackup.backup.log:

160807 05:24:35 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

160807 05:24:35 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 3306, socket: /var/run/mysqld/mysqld.sock
    Failed to connect to MySQL server: Access denied for user 'mysql'@'localhost' (using password: NO).

--auth '(null)' and Access denied for user 'mysql'@'localhost' indicate that wsrep_sst_auth is not set.

But it exists in my.cnf, as you can see. I checked sst:XXXXXXXXXXXXXX on donor node on localhost, it works.

Why joiner node does not use wsrep_sst_auth from config?


texts: SQL Server Credential Password and Secret Difference?

texts:
What is the difference between SQL Server secret and password for credentials(both server and db scope)?
For example while reading the BOL I can see specifying a secret. Is this just the same password?

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-2017


texts:
We are trying to tune our MySQL database (Myisam,  5.1.73) which is getting busy.

We investigated Key_reads:

$ mysqladmin ext -ri10 | grep Key_reads

During a normal load we have the following values (average):

Key_reads          40      (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

But sometimes, during a few dozens of minutes, we see values like this:

Key_reads          1000    (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

We have increased Key_buffer_size to 512 (which is bigger than the total of our indexes). We still have 16Gb of free RAM.

Should Key_reads be low or null?

Are these peacks normal or avoidable?


texts: How to write a query that will retrieve the items with sold amount

texts:
Problem : I have a table which has three columns such as
item id ,transaction_date,Amount_sold.

I want to have a list of item with Amount sold which are sold today but if the same item was sold yesterday then it should not come in my list.


texts:
Our SSAS cube has a date dimension, which for the DayName string member displays alphabetically instead chronologically. I added a DayOfWeek integer 1-7 to use for ordering. I have two options to accomplish this, not sure of the advantages/disadvantages of either.

Make DayName attribute visible, and use DayOfWeek as OrderBYAttribute
Make DayOfWeek attribute visible, and use DayName as NameColumn

The attributes are both functionally the same when setup like this. I'm leaning towards #2, since it's an int.


texts:
Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the transaction is rolled back...but the index incremented and was not rolled back. This results in unused sequence values, e.g. a table with indexes 1, 2, 4, 5, 7 etc. with no deletes.

Question: Is there any way to reclaim the lost sequence values, or prevent the failed function from incrementing the index?

I know that there are alternative ways to mitigate this potential problem, but I am still curious.


texts: MySQL Select rows with same id but different multiple values in another colum

texts:
Maybe my question will be silly, but i'm new to Mysql

I have a table with user_id and some interest.

+------+------+
|userID|Inter |
+------+------+
|1     |sport |
+------+------+
|2     |it    |
+------+------+
|3     |game  |
+------+------+
|1     |it    |
+------+------+
|1     |game  |
+------+------+
|3     |it    |
+------+------+
|3     |sport |
+------+------+

Amount of interests can be huge(let say 20 or whatever)

Now i need to find all userId's that have interests it, game, sport;

Of course simple AND wont work as because of different rows.

So my question will be how to do it, so the output will be

+------+
|userID|
+------+
|1     |
+------+
|3     |
+------+

Thank you.


texts: Foreign key constraint based on conditional column

texts:
I'm trying to figure out the best way to handle this situation:

I have a table set up as follows:

LEARNING CONTEXT
================
LearningContextID - INT - Surrogate Primary Key
EntityID          - INT - Foreign Key to an Entity Table
OutcomeID         - INT - Foreign Key to an Outcome Table
Context           - VARCHAR - Name of the Entity Table

I have 3 tables:
   University, Program, and Course which are all "entities".

How can I set up a foreign key constraint that will know which table to be linked to?  I suppose it's a composite key of EntityID and Context, but how can I make that conditionally constrain against the given Context's table?


texts:
SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff and never flush those log tables.

The largest table stores complete XML documents from transactions between the app and APIs of sites like eBay. I can only assume that the database being about 230 gigabytes hurts performance. I'm guessing these tables are not queried on in the app, but even so, I don't like the idea of such a huge database. After purging the log tables, I would anticipate a total remaining size of about 30GB.

I'd like some advice on how to go about this. From the little I've read on the subject, after deleting a bunch of data, the database file will not automatically shrink in size. I also read that shrinking and re-indexing are bad.

Is this large database hurting performance of other tables?
Should I do something about it?
How can I safely do something about it that will yield a performance increase?


texts: What is the difference between covering and overlapping constraints use in DBMS?


texts: "1292 Incorrect datetime value" when updating an "empty" datetime field

texts:
I have an updated_at field that was not initially set with default datetime value on the server. For a short period, a bunch of records got inserted with an "empty" value and are showing up at 0000-00-00 00:00:00 in the updated_at column. I wrote am update command to copy the values of the created_at field to updated_at and it is updating the field correctly, however, when I run it in MySQL Workbench, I get the following warning:
6 row(s) affected, 1 warning(s): 1292 Incorrect datetime value: '' Rows matched: 6  Changed: 6  Warnings: 1

My query:
UPDATE mytable
SET updated_at=created_at
WHERE updated_at='' and id >0;

I'm not sure where that warning is coming from since the created_at times are all valid and the updated_at fields are being set with the correct data.


texts: Log shipping secondaries in STANDBY stuck in 'Restoring...' - SQL Server 2012 SP3

texts:
Yesterday, I configured Log shipping between 2 SQL Server 2012 SP3 servers for a total of 13 databases.

Some specifics:

I'm using 'Azure files' to transfer the log files (CIFS accessible BLOB storage)
I did the configuration using SSMS
All backups got transferred and restored just fine
STANDBY mode (and disconnect users) was used on all databases,
I verified this in msdb.dbo.log_shipping_secondary_databases
The log shipping process worked for all databases yesterday (they were all in read only mode after the first transaction logs were replayed on the secondary (also verified in msdb.dbo.log_shipping_secondary_databases).

This morning, I noticed the following:

Only 5 out of my 13 databases are in read only mode.
The rest is stuck in 'restoring' state.
The databases that are stuck, are those that have a very low load (or possibly no load at all)
In msdb.dbo.log_shipping_secondary_databases, the last_restored_date is not current with the last execution of the agent jobs for the DBs that are stuck.
The agent jobs seem to have executed without a problem (job succeeded) on those databases.

Can somebody give me a clue as to what's going on here?


texts: How to insert from one table to another from trigger

texts:
So i have an after insert trigger to insert some data into a temp table, and i want to add to the end of the trigger the ability to insert the rows from the temp table back to the original table. This is a workaround for the mutating tables error. Thanks for the help.

CREATE OR REPLACE TRIGGER podium_core.trigger_field_tag_sync
AFTER INSERT
    ON podium_core.pd_field_tag
    FOR EACH ROW
    BEGIN
        insert into podium_core.pd_field_tag_temp
        (   field_nid,
            tag,
            version,
            modifiedby,
            createdby)
        select distinct FOO.field_nid, :NEW.tag,0, 'trigger','trigger'
        from
        (select child_field_nid as field_nid
            from podium_core.pd_field_pc_rel
            where parent_field_nid = :NEW.field_nid) FOO

        where :NEW.createdby <> 'trigger';

        delete from podium_core.pd_field_tag t1
            where (t1.nid, t1.tag, t1.field_nid) in
                    (select t2.nid, t2.tag, t2.field_nid
                        from podium_core.pd_field_tag t2
                     where t1.nid < t2.nid
                        and t1.tag = t2.tag
                        and t1.field_nid = t2.field_nid);
    END;
    /

Here is the temp table

create table podium_core.field_tag_temp
            (NID NUMBER NOT NULL,
                ENTITY_NID NUMBER NOT NULL,
                TAG VARCHAR(1024) NOT NULL,
                CREATED_TTZ TIMESTAMP(6) NULL,
                MODIFIED_TTZ TIMESTAMP(6) NULL,
                VERSION NUMBER NULL,
                MODIFIEDBY VARCHAR(512) NULL,
                CREATEDBY VARCHAR(512) NULL,
                IS_EXTERNAL NUMBER(1) NOT NULL);


texts: Select fixed number of records/rows from growing table from SQL Server?

texts:
Background: A SQL-Server database table is used to store weekly reports of some system memory usage, meaning that each week a new table row/record/line is inserted.

The question is how a fixed number (e.g. 5 records) of records can be queried, with the side-condition that those records are equally spaced within the time-range.

if this was the table

id | memory
-----------
01  1231
02  1241
03  1260
04  1300
[.... other 92 rows ...]
97  2001
98  2002
99  2020

and with said 5 records I would like have a table as such

id | memory
-----------
01  1231
25  1600
50  1800
75  1900
99  2020

since it would most represent the overall picture of the data.


texts:
I have a table with ID's, dates and values witch are the results of periodic tests.
I can find the latest test with the according result.
But I need also the initial date since when the same result is marked.

ID  Date       Value
01  2015-01-01  2
01  2015-02-01  3
01  2015-03-01  3
01  2015-04-01  2
01  2015-05-01  2
01  2015-06-01  1
01  2015-07-01  3
01  2015-08-01  3
01  2015-09-01  3

So here the last date and result for ID 01 are 2015-09-01 and 3
The initial date that I also need is 2015-07-01 as that is the initial dat of the last serie with value 3

How can I write this query?

I get the last date and score by ID using this query:

select * from tblScores s
join (
    select id, max(Date) as MaxDate
    from tblScores group by id
) Q on s.id = q.id s.date = q.maxdate


texts: Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

texts:
I'm helping a friend with setting up encryption of data on SQL Server 2008 R2 Standard edition. Upon original research I thought I could use
TDE but did not realize that it was only available for Enterprise or DataCenter versions of SQL Servers. Upon further research into SQL Server 2008 R2 features I saw that it does allow for "Data encryption and key management" but I'm not sure what it means or how to implement it.

What is the most efficient and low cost method for data encryption? Should I do something through SQL Server or just use third party tools to encrypt the whole volume where DB and backups are? Also if someone can point to a way to use "Data encryption" that comes as feature in standard edition I would really appreciate. Every time I search for encryption on sql server I keep ending up on how to use TDE and in current scenario it is not feasible for the size of business to purchase Enterprise Edition.

Reason for encryption - HIPAA Compliance. If access is gain to file system for database or backups, data is encrypted and can not be of any use. This could include physical access to the machine or access through local admin.


texts: Can you restore a ScyllaDB from only a backup of /var/lib/scylla/data folder?

texts:
I had a single node scyllaDB which was lost, but only a backup of the /var/lib/scylla/data folder was done.
My question is, can a single node scylla be restored from only this backup? I already checked and the files don't contain any snapshots


texts: After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind?

texts:
MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - storage. But the disk space didn't change a bit after this compression. I need to gain some space on the disc and wonder if shrinking any kind (file, database) would help.

I would like to hear your advises.
Thanks


texts: calculate and return the ratings using sql

texts:
I have a table like this

What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the weighted rating is
((5x3) + (4x1) + (3x1) + (2x1) + (1x0)) / (3+1+1+1) = 4
for service uuid ff0177fe. How can I achieve this ?


texts: SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

texts:
This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this works.

Essentially, I have a series of forms in a project, all of which have a status assigned to them. The statuses have an assigned order of precedence, which will trickle up to the higher level visits. The way this is currently working uses the following sql statement:

select state_code
 from (select ' || v_priorstr || '
    from m_object mo, m_form mf
      where mo.id = mf.id
        and mf.formlayout_id = :1
        and mo.v = :2)
 unpivot
   (
    state_counts
    for state_code in (' || v_priorstr || ')
   ) where state_counts = 1 and rownum = 1

where v_priorstr is a comma separated string of the statuses in order of their precedence. As an example, consider the following string: "Blank,Error,InProgress,Completed". The m_object table contains each of these columns, with either a 0 or 1 to show which statuses apply. (Forms can have more than one status) This query selects the highest priority status that it will then use to display a status icon for that form.

The problem arises when dealing with repeating forms (which can have multiple iterations of themselves under one link). They all share a single status icon in the navigation system.

While normally this works fine, since SQL will naturally display columns in the order you request them, and they maintain that order when unpivoted. However, with the multiple forms, and thus multiple rows, all that statuses of the first form/row take absolute precedence over the statuses of all the forms.

For example, if I have two of these repeating forms, the first of which is 'Completed' the second is 'Error', the above query (sans the rownum = 1) will return the following two rows:

Completed
Error

I have thus far attempted the following solutions:

Group By without an Order By throws the order off.
I can't use a case-based Order By because the status names can change.

In short, I need it to either sort by or group by the status names, in the order that is passed in via that string.


texts:
i am implementing restore points using pg_create_restore_point, and will then have a set of scripts to enable my users to restore to one of these restore points. Clearly i have work to do with taking backups and archiving WALs etc, but this is all in hand.

my question is, is there a database table which lists all the available restore points?

cheers
dave


texts: Easier way to change every user in every database to default schema = dbo?

texts:
There was recently a domain migration where our SQL Server is hosted.

I have two servers and about 30 DBs on each. Every user was changed to the new domain but in doing so, their default schema changed from to their domain when we want in defaulted to DBO.

I'm currently changing them manually by clicking on a DB -> Security -> Users then in the general properties tab, manually typing dbo in the default schema box.

Is there a way to write SQL code to run and adjust the default schemas for all of my users across all DBs?


texts: mysql won't start, it says Table mysql.plugin doesn't exist

texts:
I've had this problem for several hours, and now I'm just tired..

When I try to start MySql from terminal (mac), i get

ERROR! The server quit without updating PID file (/usr/local/mysql/data/127.0.0.1.pid).
There seem to be something wrong with the table mysql.plugin, log file says it doesn't exist.

I have uninstalled and reinstalled mysql on several ways, trying every method i have found googling around. Therefor i think there must be something wrong with the computer - not the mysql software.

Latest install is from mysql.com, a dmg-file.


texts: Can data changes become visible between a subquery and an outer statement in PostgreSQL?

texts:
I have a PostgreSQL query that looks like this:

DELETE FROM x WHERE x.id NOT IN (SELECT x_id FROM y WHERE ...);

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

My intuition is that since the subquery is part of the overall statement, no data changes should be visible to the DELETE that weren't already visible to the SELECT. Is this correct?


texts: T-SQL - AOAG Health state Healthy or Not Healthy in a Single row

texts:
I am trying to write a small piece of code which should give the status of the AOAG SQL Server 2014 Health or not healthy based on below two conditions .

Mine is 3 Node replica AOAG (including BCP Replica).

Conditions I am looking for :

If the synchronization_health_desc is "Healthy" for all three replicas , I should get the result as below :

If the synchronization_health_desc is "Not Healthy" for any of the three replicas , I should get the result as below :

So far I have tried below :

select dns_name as AG_Listener ,
(select top 1 group_name  from sys.dm_hadr_availability_replica_cluster_nodes  ) as AG_Gorup,
synchronization_health_desc as AG_Status
from sys.dm_hadr_availability_replica_states rs
INNER JOIN  sys.availability_group_listeners gl ON rs.group_id = rs.group_id

The output is below . But I want only one row as result which says overall result as Healthy or Not Healthy based on above two conditions .


texts:
I want the equivalent of this for ints.

--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0

So something like this.

--Invalid SQL
DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
select * FROM Person AS p
WHERE p.ixPerson IN(@users


texts: I found out that on an instance of a certain SQL server at my company- sa is virtually the only login used by all the sessions. The SPID# extends to 130. What do you think is the impact of this? Is there any downside despite of course security? Appreciate you pitching in on this. Thanks.


texts: How to redefine the SQL of a postgresql view without invalidating the programs using it

texts:
I have a complex piece of code that performs a FIFO allocation.  i need this logic in 3+ different places in my application to link 3 different sets of tables together.  in MSACCESS, i can accomplish this by opening a recordset with a dynamically defined viewname (the underlying view is previously created, but in the program i can use a variable name in the call that sets up the cursor).  that way i can use the same program to do the FIFO for all 3 sets of tables.
In Postgresql, i have not found a way to use a variable name in the SELECT statement that sets up the Cursor in the program.  (If there is such a way, that would be a perfect solution for me).  I attempted to just dynamically redefine the query using CREATE OR REPLACE on the view at the beginning of the program, but that renders the program invalid until recompiled.  since i need to do all 3 in a single session, i cannot recompile the program while running it.
I am currently looking at inserting all of the subject records into 3 tables specific to this program, but the overhead of doing that could be significant (some of the tables have 1,000,000+ rows in them).
Any ideas?
Thanks in advance!


texts: Exporting partial table records as INSERT script

texts:
I have datatable with a large amount of data in it and I'm trying to export the data from it. I saved the file locally, but I cannot open it with Notepad++. The problem is that I just want to get some of the data as an INSERT script but I don't know if this is possible.

Can anyone tell me if it's possible to get some of the data from a table as an INSERT script using SQL Server 2014?


texts:
Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance.

Would I need to change the applications select statements for the application to continue to work?
If it would continue to work with the existing SQL, would I need change the SQL in order to gain any advantage from the partitioning?
What can I expect to happen if my application is using a Database Abstraction Layer, where I would normally not know if the DB had been partitioned?


texts: MySQL Select additional rows if initial query has specific column value returned

texts:
I have this sample DB table:

id  type    c_owner_id  c_linked_id
1   parent  1           2
2   parent  1           3
3   related 2           3

My script is provided a c_id. I SELECT out all rows where this c_id is the c_owner_id OR c_linked_id.

What I would like is to have in the same query an additional SELECT if the c_linked_id equals the provided c_id AND type equals 'parent' that would select the rows that have a relationship with the same parent.

Pseudo SQL would be:

SELECT * FROM
    table_name AS orig
WHERE
    orig.c_owner_id = $c_id
    OR orig.c_linked_id = $c_id
IF
    orig.type = 'parent' AND orig.c_linked_id = $c_id
THEN
    (
        SELECT * FROM
            table_name AS sub
        WHERE
            sub.type = 'parent'
            AND sub.c_owner_id = orig.c_owner_id
    )

Is this type of "IF THEN SELECT" statement possible in MySQL?


texts: MySQL "Limit" clause grabbing more results than requested

texts:
I'm trying to add pagination to my points system (100 results per page) and came up with this :

SELECT A.username, A.points
FROM users A
INNER JOIN (
   SELECT points
   FROM users
   WHERE points <> 0
   ORDER BY points DESC
   LIMIT $start, $limit
)B
USING (points)

The username and points columns have indexes.

When running this query (LIMIT 100, 100), it returns this (in phpMyadmin) :

Showing rows 0 - 29 ( 124 total, Query took 0.0001 sec)

I'm asking for 100 rows, but it's returning 124. I've never seen this before.

Here is what EXPLAIN shows :

+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref      | rows | Extra                    |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL   | NULL    | NULL     |  100 |                          |
|  1 | PRIMARY     | A          | ref   | points        | points | 2       | B.points |    2 |                          |
|  2 | DERIVED     | users      | index | points        | points | 2       | NULL     |  200 | Using where; Using index |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+

EXPLAIN shows 100 rows as well, but when outputing the array, it's still returning 124 results.

Any ideas how I can fix this?


texts: oracle-mysql fast refresh materialized view

texts:
Is is possible to Create Fast refresh enabled Materialized view from MySQL to Oracle .  i know steps for oracle to oracle Fast refresh enabled Materialized view. But now the scenario is need to transfer number of records from MySQL to oracle via db-link with use of materialized view. But every time i need to make Complete Refresh which is time consuming.
so is it possible to create fast refresh materialized view for MySQL to Oracle ?


texts: MS DTC errors on SQL Server 2008 R2 but not SQL Server 2000 running on difference instance

texts:
I am receiving errors when I try to run a distributed query against different SQL Server 2008 R2. At first I thought this might be cause the servers have been set up on a VM using a clone but after I discovered that I could run this query on SQL Server 2000 but it fails on a SQL Server 2008 R2 on the same server I am beginning to think that there is something I am just missing.

It does run without any issues on another developers network but it isn't running VMs

Steps I have done so far:

Totally uninstalled MS-DTC, rebooted and installed MS-DTC and another reboot.
Ran Dtcping - no errors
Checked to make sure there are no duplicate SIDs
Asked my non geek wife. She changed the subject to shopping
Rechecked the link servers - everything checks out.
Ran profiler on the transaction - no information given.
Checked the MS-DTC on production boxes to look for anything I might have missed. Also checked out from sp_configure from production boxes to look for anything that I might have missed.
uninstalled/reinstalled MSDTC just in case.
Made sure that the security on DTC isn't stopping me from doing anything.
Firewalls are off on every server I had played with.
Got caught googling MSDTC on my phone while having lunch with my wife.
Updated my servers for with any missing patches.
Talked to my dog about it. At least he listen but didn't have any answers.
Picked up everything and tried it on a different server. No change.
Read the logs from MSDTC to find out that the parent node is the one asking for the abort request


texts:
I have an assignment which requires me to design a database.

I have 3 entity types WARD, BED, and BED_ASSIGMENT (which is the "composite" entity type between BED and WARD).

The BED primary key consists of the attributes bed_id and ward_id (each ward has bed number 1 for example, so I need ward_id to properly identify it)

My question is:

I have two attributes: total_bed_ count and total_vacant_bed_count. In which entity type should I place them?


texts:
I am practising in writing a simple forum and just can't decide on table structure. Currently I have this:

    id  int(11)     AUTO_INCREMENT
    title   varchar(56)
    group_id    int(11)
    group_parent    tinyint(1)
    access_from int(11)
    alliance_forum  tinyint(1)
    alliance_id int(11)
    alliance_role_access    int(11)

Group parent is a 0 or 1 column which states wherever a forum is parent of forums, and group id is for group forums whch reffers to same table to record with group parent set to 1. Alliance forum is same as group parent, as the alliance id,and access column are for access control. Which would be the best and perfomant way of restructuring this? Should I mak 3 different tables for alliance forums, forum_groups and group_forums or keep it this way?
P.s. Please remove my
another same question.


texts: Login failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT: ] issues

texts:
Over the last 10 days or so, I've started to get 'Login failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT: ]' entries nearly every 5 mins in my SQL 2008 logs and I can't figure out why.

I've done the trace and this is falling down on accessing the master db every time and it's allegedly coming from my IIS - but my web app (via user access) isn't connecting to it at the times stated in the log.

What I don't understand is that it's trying to use my user-created SQL login, which connects to the master DB and all of my other SQL DBs fine without problems when I do an ASP connection string and/or use SQL Enterprise Manager.

I've looked at the login's details in SQL Server Logins and although it wasn't user mapped to the master db it never has been.

Does anybody know how I can track what is calling it from within IIS?

Thanks

Tom


texts: Postgres asynchronous procedure execution?

texts:
I have an ETL script entirely contained within a Postgres 9.3 procedure.  The script takes several hours to process, which is fine for our purposes.

What I haven't been able to find out is if there is a method within postgres to call a procedure, and then let it run on the server even if the client making the original call becomes disconnected.


texts: Can graph databases be easily distributed?

texts: I have never worked with graph databases such as neo4j, I would like to know if they support easily replication and clustered distribution as well as key-values databases, or if there inherent structure makes it really difficult/ slow


texts: How do I monitor which Windows user is logging in as SA?

texts:
Possible Duplicate:
  ProcessID Owner with third party app

Several of our applications use the sa account to login to our SQL Server 2008 instance. As these applications are poorly designed enough to use sa as the login, so are they poorly designed enough to use the .Net SQLQuery Data Provider for their application name.

Our system is also thin-client-ish. In essence, all requests show up from the same host, that most users RDP into.

Recently, one of them has been forcing the database server to 90% CPU for up to 15 minutes at a time. But, I can't figure out who is doing what to cause it.

So: is there a way for me to tell which Windows user the application that is logging in as sa is running under? If yes, I can just ask them what they're doing.


texts:
I'm developing a system to back-up computers and store the files / data with retention for a while. The actual files are stored in an object store, while I'm planning to keep the metadata of the files in MongoDB. I am however rather new to MongoDB.

As there will be many computers containing many many files, there will eventually be billions of (small) records / documents. I'd like to use MongoDB for its sharding features.

For each item I'm storing the following data items: Computer_ID, objectstore_ID, path, filename, size, last_modified, revision_start, revision_end. An example document in the collection would be:

{"computer_ID": 6829, "objectstore_ID": "abcdefghijklmnopqrstuvwxyz", "path": "/home/user/path/to/directory/", "name": "file.txt", "size": 58202, "last_modified": 1395491119, "revision_start": 52, "revision_end": 58 }

Typical queries would be listing a directory on a computer in a specific revision, for example {"computer_ID": 312, "path": "/home/", "revision_start": >=54, "revision_end": <=54 }. Usually those four columns will be used in queries.

My first question is if MongoDB is the right solution for this use case, or would a relational database suit my needs better? In case of the latter, how could I distribute the data over multiple servers like MongoDB has sharding?

Secondly, what would be the best indexing strategy to maintain the best (or at least reasonable) performance when the database grows. Should I add 4 separate indexes on computer_ID, path, revision_start and revision_end? Or create one multikey containing all four? Maybe something else?

Finally, I also wonder whether the storage size of the indexes won't grow to a multiple of the original data size.


texts: Connecting to Oracle Pluggable database using service name

texts:
I installed Oracle 12c on windows using all the defaults but choosing to use a pluggable database and to install the sample data. The container database and SID name is ORCL and the pluggable database is PDBORCL.

I noticed that using SQL Developer I can connect to the pluggable database by using its name as the service name. Also I can connect to the container database using its name either as SID or service name.

Looking at my tnsnames.ora I found that only ORCL is mentioned as a service name. Does oracle register pluggable databases as service names automatically or there is a configuration file that I am missing?


texts:
I have UTF8 data stored in Postgres.  I can query it with a Perl script and display it properly.  When I use the Postgres client, it displays like the encoding is off.  I'll see characters displayed like \u0087\u0081.

From inside the database:

=> \encoding
UTF8

In the shell (I manually set LANGUAGE as I read elsewhere that might be a fix.):

$locale
LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

This is in Ubuntu 16.04.

What setting have I missed?

Edited to add code populating psql:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

### Source DB setup
my $maria_database="srcdb";
my $maria_user = "";
my $maria_password = "";
my $maria_host= "localhost";

my $mariadbh = DBI->connect("dbi:mysql:database=$maria_database;host=$maria_host","$maria_user","$maria_password",{AutoCommit=>1,RaiseError=>1,PrintError=>0,mysql_enable_utf8=>1});

my $mysrcquery = "SELECT fields from sourcetable limit 2;";
my $src = $mariadbh->prepare($mysrcquery);

### Destination DB setup
my $postgresql_database="desdb";
my $postgresql_user="";
my $postgresql_password="";
my $postgresql_host="localhost";

my $dbh = DBI->connect("DBI:Pg:dbname=$postgresql_database; host=$postgresql_host", "$postgresql_user", "$postgresql_password", {AutoCommit => 0,pg_enable_utf8 => 1});

my $pginsertquery = "insert into desttable (fields) VALUES (?)";
my $pginsert = $dbh->prepare($pginsertquery);

$src->execute();
my ($col0);
$src->bind_columns(undef, \$col0 );

### Loop through results and insert in to psql
while($src->fetch())
{
  print $col0;
  $pginsert->execute($col0);
}

$dbh->commit;
$dbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";
$mariadbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";

Test sqldump:

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.1
-- Dumped by pg_dump version 9.6.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: public; Owner: testuser
--

CREATE TABLE test (
    testdata text
);

ALTER TABLE test OWNER TO testuser;

--
-- Name: test id; Type: DEFAULT; Schema: public; Owner: testuser
--

ALTER TABLE ONLY test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: testuser
--

COPY test (testdata) FROM stdin;
Ð¿Ñ<80>Ð¸Ð²ÐµÑ<82>Ñ<81>Ñ<82>Ð²Ñ<83>Ñ<8e>
\.

--
-- PostgreSQL database dump complete
--


texts:
I have MySQL 5.0.27 running on a Linux server. It is consuming too many resources (it is running about 80 tasks loading a database). I am unable to log into the server to reduce the number of tasks because SSH, while accepting a password, will time out while requesting a new channel because of the system load.

Can I remotely send a shutdown command to the server? It is possible from another server to either use mysql or mysqladmin with a -h option or use MySQL workbench to shut the server down gracefully?

If I reboot the server I will need to repair two large tables (>200m records) which takes hours. I'd like to avoid this option.


texts: How can I hide a column using a Select statement?

texts:
declare @bid varchar(4) = '1007'

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    sr.route_description

from sri_schedule_releases_view as sr

where
    ((case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))

order by sr.item_id asc

This query works fine. But I want to display sr.route_description only who has the sr.route_code PM 01, PM 03 and PM 05.

Are there any possibilities to do this?


texts:
I finally got my hands on a copy of SQL Server 2008 R2 Developer Edition.  I have installed an instance on my server at home.  I wanted to get all the Management Tools (SSMS, Profiler, etc.) on my laptop so I removed my copy of SQL Server Express.  Then I installed the Management Tools (Complete) on my laptop.  It's great, I can hit the instance and everything.

But for the times when I'm away from my local network and not able to connect to my server's instance, what is recommended?  Should I reinstall an install of SQL Server Express just for testing purposes?

I guess what my question is, is SQL Server Express less taxing on my laptop than if I was to install a full version instance?  Or is it equivalent to installing my Developer Edition?

I could always install the Developer Edition (Enterprise) on my laptop and have the SQL Server services start manually so I'm not hogging resources when I am connected to my local network (and server that houses my active instance).

What is advisable here?


texts:
I'm studying for a database class and came across this EERD:

Very simple EERD however, I'm wondering- how can I go about and convert this to a simple ERD instead? From my understanding, the only thing different about an EER is that it shows the specialization, generalization, partitioning, and aggregation - for this above diagram: specialization of course.

So if I were to remove the specialization entities for VEHICLE (Motorcycle, Car, SUV, Truck), would this diagram then be considered an ERD? That wouldn't make sense though, wouldn't it be important to show those relationships on an ER diagram?

If someone can tell me how I would go about converting this to an ER diagram, that would really help.


texts: Connect to oracle 11 on a corporate network via oracle developer 17

texts:
Sorry for this absolute oracle noob question:

I am in an environment that uses integrated / active directory authentication, for developer to access the oracle databases via an old version 9 PL/SQL Developer:

Clicking OK gets me into the dababase

I am trying to connect to the same database via Oracle SQL Developer 17 and the login screen is more complicated:

What do I enter here? I've searched far and wide but there are few resources for an oracle noob like me.


texts: HkHostDbCtxt::Initialize(): Database ID: 16 'MyDB'. XTP Engine version is 0.0

texts:
In error log on SQL Server 2016, i am seeing lots of error message like this.

  2017-08-14 20:20:41.820   spid95  [INFO] HkHostDbCtxt::Initialize():
  Database ID: [16] 'mydb'. XTP Engine version is 0.0. 2017-08-14
  20:20:41.820  spid95  Starting up database 'mydb'

Similar message for other database as well. Please help to understand what the message is about, is it a normal or is my databases crashes frequently? Also please suggest the solution.

.


texts:
I have an events table in which various events could be happening in time slots each day. What I mean is each event has many time slots each day of the week. For example, event A can be have slots such as 1-2 pm, 5-6 pm on Mondays, 2-4 pm, 5-7 pm on Tuesdays etc.

What's a good way to structure this in postgres taking into account that queries could be on events by timings as well. For example, find all events on Monday which occur between 5 - 7 pm.

I was thinking of having a second table with event id as the first column, day of the week in the second column and then it's timing in the third column. So events having multiple timings in a day would be stored in individual rows for each timing on each day.

Alternatively, I was also thinking of a table with first two columns same as above, and a third column of array of timings, so events having multiple timings in a day would be stored in just one row for each day.

Which one is better or is there an even better way? If the former is better, in what scenario is use of arrays in databases useful? IMO, anything that can be done with arrays can be done with splitting tables or storing in different rows which are rather helpful when querying.

Also, how would I go about storing time such as 5-6 pm etc in the databse? I've a fair idea about storing the day of the week by using 7 bit boolean value. So Sunday would be 0000001, Sunday and Monday would be 0000011 etc, but how would I store the time ranges?

Thanks!


texts: Plan guide validation with fn_validate_plan_guide gives false positives

texts:
When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name '#test'.

But the the plan guide still pushes the query hint into the SQL and the desired execution is achieved.

Does this highlight a problem with the fn_validate_plan_guide function?

The script below recreates the problem.

--Enable the actual execution plan before running the query so the plans can be compared

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    CREATE TABLE #test
    (
        ID INT
    )

    INSERT INTO #test
    SELECT ROW_NUMBER() OVER(ORDER BY job_id)
    FROM dbo.sysjobs

    SELECT *
    FROM #test t
    JOIN #test t2 ON t.ID = t2.ID

    DROP TABLE #Test
END
GO

--Execution before the plan guide is created will have a hash join in the second batch
EXEC msdb.dbo.test
GO

--Create the plan guide
EXEC sp_create_plan_guide 'test',
'   SELECT *
    FROM #test t
    JOIN #test t2 ON t.ID = t2.ID',
'OBJECT', 'dbo.test', NULL, 'OPTION (MERGE JOIN)'
GO

--Validate the plan guide. This returns the error "Invalid object name '#test'."
SELECT
    plan_guide_id, msgnum, severity, state, message,
    name, create_date, is_disabled, query_text, scope_type_desc, scope_batch, parameters, hints
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO

--Execution after the plan guide is created will have a merge join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'DISABLE', 'test'
GO

--Execution after the plan guide is disabled will go back to having a hash join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'ENABLE', 'test'
GO

--Execution after the plan guide is re-enabled will go back to having a merge join in the second batch
EXEC msdb.dbo.test
GO

--Clean up
EXEC sp_control_plan_guide 'DROP', 'test'
GO

DROP PROCEDURE test
GO

Is this function giving errors that are false positives or are these just warnings that the plan guide may fail or is it something else I haven't thought of?

I have created a Connect item here with the text above but am yet to have a response.


texts: use of signal for insert statement in mysql stored procedure

texts:
I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure:

DROP PROCEDURE IF EXISTS `spItem`$$
CREATE PROCEDURE `spItem`(
       IN p_itemname VARCHAR(50),
       IN p_itemvalue smallint(6),
       )
BEGIN
      INSERT INTO item VALUES('',p_itemname,p_itemvalue);
END$$

How can we use the SIGNAL statement for each kind of error in the above stored procedure that checks for the following:

1. check for any error in the insert statement
2. check for duplicate item names
3. check for valid item values (as it should just accept numeric values).

What changes will be required in the above stored procedure to handle the above errors?