texts: The best MySQL configuration for my server

texts:
I'm little newbie in configuring mysql and I need some advices.

Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc.

I'm using it to sharing files per samba and hosting my local projects in PHP/MySQL - I'm programmer, and I'm often testing new solutions etc

It is working good, but INSERTS on InnoDB tables are slow as hell.

I think it is bad config in my.cnf, becouse a few months earlier I used the same computer on Windows WAMP and queries were faster...

Some information:

CPU: Intel(R) Pentium(R) 4 CPU 3.00GHz
RAM: 1GB
HDD: Maxtor 6L200M0 (189GiB (203GB))

Actual my.cnf:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 32M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=80M
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M


texts: Building year to date aggregations using SQL

texts:
I need some help with building a monthly aggregation using only SQL.

Imagine the following table:

TranID  DateCode    Account Value
   1    20140101        1   5
   2    20140106        1   -3
   3    20140207        1   6
   4    20140409        1   3
   5    20140103        2   3
   6    20140215        2   7
   7    20140519        2   6

There are two accounts that have transactions on various dates. I would like to write code that gives me this result assuming we are in July:

MonthID     Account YTD
20140101        1   2
20140201        1   8
20140301        1   8
20140401        1   11
20140501        1   11
20140601        1   11
20140701        1   11
20140101        2   3
20140201        2   10
20140301        2   10
20140401        2   10
20140501        2   16
20140601        2   16
20140701        2   16

I'm thinking I should be able convert the dates to MonthCode using

DATEADD(day, -DAY(DateCode) + 1, DateCode) AS MonthCode

I'm thinking I should be able to solve this somehow by joining the table to itself but I don't really get the numbers right. Also I need to somehow get the months without transactions in as well..

Any and all help will be greatly appreciated!

Code to generate the mock data:

Create table #Tran(
TranID int identity(1,1),
DateCode Date,
AccountCode varchar(50),
Value int
);

insert into #Tran (DateCode, AccountCode, Value)
values ('20140101', '1', 5),
('20140106', '1', -3),
('20140201', '1', 6),
('20140401', '1', 3),
('20140101', '2', 3),
('20140201', '2', 7),
('20140501', '2', 6);


texts: How is the 2 Node Cluster Limit Enforced for SQL Server Standard Edition?

texts:
I know Books Online says that standard edition only supports 2 nodes but how is this actually enforced?

Will it:

Not let you install a new instance on a cluster with more than 2 nodes?
Not let you add a 3rd node to a failover cluster that has at least 1 SQL Server standard edition instance?
Not let you select more than 2 possible owners for the resource group / cluster role?

Or will it behave in some other way?

I'm curious because one of the admins would like to expand an existing cluster from 2 nodes to 3 nodes but it has 2 standard edition instances of SQL Server on it so I don't know what will happen.


texts: Sybase internal tables for ORM generation

texts:
I am working on a Perl application that uses DBIx::Class::Schema::Loader in order to generate the ORM layer of the application, against a Sybase ASE 15.5 database.

We recently migrated from Sybase 12.1, where the ORM generation worked fine, to our current database, where things have gone screwy.  Our ORM layer doesn't change much, so this wasn't immediately apparent.

The driver for accessing the database information uses the syscomments, sysobjects, syscolumns, and systypes tables, but I can't find any reference to them in the ASE 15.5 system table documentation.

Searching through the documentation has led me to conjecture that these tables were retired sometime around 15.1, but I don't have enough expertise to be able to comment either way.  Does anyone know where this information is stored in 15.5?


texts: Can the Data Export Service in Microsoft Dynamics export data to an Azure Synapse Data Warehouse?

texts: I am able to use the Data Export Service (DES) to export data to an Azure SQL Database. When I try to create a new profile in DES that connects to a Data Warehouse (aka Azure Synapse Analytics) on the same Azure SQL Server using the same user (db_owner member), I keep getting the message 'Error validating profile registration'. Note that the Key Vault Secrets used for each connection are virtually the same except the 'Initial Catalog' and the DES wizard is able to validate the connection to the Key Vault.


texts:
I am using Postgresql 9.5 on Ubuntu 16.04 LTS.
I receive the below error when I type psql:
a

psql: could not connect to server: No such file or directory
  Is the server running locally and accepting
  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

On checking the logs in /var/log/postgresql/postgresql-9.5-main.log, I see the error as:

  2018-11-26 13:17:41 IST [3508-1] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied

Below are the permissions of etc/ssl/private and ssl-cert-snakeoil.key files:

vivek@vivek-ThinkPad-E480:~$ ls -l /etc/ssl
total 36
drwxr-xr-x 2 root root     20480 Nov 22 13:06 certs
-rwxr-xr-x 1 root root     10835 Dec  8  2017 openssl.cnf
drwxr--r-- 2 root ssl-cert  4096 Nov 22 13:06 private

vivek@vivek-ThinkPad-E480:~$ sudo ls -l /etc/ssl/private
total 4
-rw-r----- 1 root ssl-cert 1704 Nov 22 13:06 ssl-cert-snakeoil.key

Can anyone guide me in the right direction in setting permissions?

UPDATE #1: postgres user is also added to the group ssl-cert.

vivek@vivek-ThinkPad-E480:~$ getent group ssl-cert
ssl-cert:x:112:postgres

UPDATE #2: I found that there is no server.key present in /var/lib/postgresql/9.5/main.


texts: Look for software that can convert sql (mysql) to erd

texts:
I would like to have an ER Diagarm in phpmyadmin, however, its graph is not up to my expectation, the graph has no relationship indicator (1-1) (1-m) etc at all.

So i am wondering whether there is a software that can transfer the sql/ any kind of file exported from phpmyadmin to a diagarm? Thank you for your help.


texts: How can I store latitude and longitude without PostGIS?

texts:
I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a map. I am using HTML 5 mapping plus Google maps. I have converted the addresses I have, into long/lat and they are stored in a CSV file along with other attributes.

I have tried storing as point, character varying and number as well as trying to create my own definition but have had no success. The table already exists and I just need to add these additional columns. I would rather not use PostGIS since that feels like adding in an additional level of complexity which I could well do without.

So what do you suggest?


texts:
I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels.

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     NULL
4505    4485    3     NULL
5066    4505    4     NULL
6121    5066    5     NULL
5068    6121    6     NULL

My goal is to grab the property of the Parent that isn't NULL. So in this case, this should be returned:

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     325
4505    4485    3     325
5066    4505    4     325
6121    5066    5     325
5068    6121    6     325

This is what i have tried so far.

;WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
    FROM #MappingPartOne m
    WHERE m.Functional_Location_Key IN (6121,5068,5066,4505,4485,1399,1393)

    UNION ALL

    SELECT
        t.Functional_Location_Key
        ,t.Parent_Key
        ,t.Level
        ,t.Property_Key
    FROM CTE t
    JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
option (maxrecursion 0)

Except this causes an infinite loop.

Thank you for your help.

I have setup a table variable for your testing

DECLARE @MappingPartOne TABLE (
    Functional_Location_Key int,
    Parent_Key int,
    Level int,
    Property_Key int
)
INSERT INTO @MappingPartOne VALUES (1393,   NULL,   0,  2)
INSERT INTO @MappingPartOne VALUES (1399,   1393,   1,  325)
INSERT INTO @MappingPartOne VALUES (4485,   1399,   2,  NULL)
INSERT INTO @MappingPartOne VALUES (4505,   4485,   3,  NULL)
INSERT INTO @MappingPartOne VALUES (5066,   4505,   4,  NULL)
INSERT INTO @MappingPartOne VALUES (6121,   5066,   5,  NULL)
INSERT INTO @MappingPartOne VALUES (5068,   6121,   6,  NULL)


texts: Why if a transaction unlock a data item immediately after its final access of that data item, then serializability may not be ensured

texts:
The quote below is from Silberschatz's Database System Concepts. It says if a transaction unlock a  data item immediately after its final access of that data item, serializability may not be
ensured. Can you please explain why serializability may not be ensured?

Transaction Ti may unlock a data item that it had locked at some earlier point.
Note that a transaction must hold a lock on a data item as long as it accesses that
item. Moreover, it is not necessarily desirable for a transaction to unlock a data item
immediately after its final access of that data item, since serializability may not be
ensured.


texts: Is there any AWS RDS Utility to Manage Master Slave mysql db with read write splitting?

texts: I am looking for a solution for production which can manage the read and write splitting on master and slave connection as i can't affford to define multiple connection string for R/W in whole application.


texts: Do operations on indexed views happen in parallel?

texts:
When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel.

Is this true?
Why do the %s not add up to 100%?

Below is somewhat of a contrived example.

/*
DROP INDEX [IX__AView] ON [dbo].[_AView]
DROP VIEW _AView
DROP TABLE _A
*/

CREATE TABLE _A (Name VARCHAR(10) NOT NULL)
GO

CREATE VIEW _AView WITH SCHEMABINDING AS
SELECT Name FROM [dbo]._A
GO

CREATE UNIQUE CLUSTERED INDEX [IX__AView] ON [dbo].[_AView] ([Name] ASC)
GO

/*
--run the estimated execution on this
INSERT INTO _A (Name) values ('cheese')
*/


texts: Display user-defined types and their details

texts:
I've created a few new UDTs in PostgreSQL. However, now I have two problems:

how to see which UDTs have been defined?
how to see the columns defined within these UDTs?

Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.


texts:
I have a server with SQL Server 2008 R2 installed in it. This server is used as a D -1 Database, in other words, the database it is one day behind from production server.

The problem is that my production database has more than 300GB and the daily restore is becoming an issue for the team who needs this server, because the job isn't finished before the morning day.

Is there some solution that replace the daily full backup, some kind of replication with delay ?


texts: Very poor MySQL query performance after upgrading from server-5.5 to server-5.6

texts:
I'm in the process of trying to migrate a database from an old server to a new one. The old server is running an Xampp installation which has been more than adequate for the use case. For the new server I installed a newer version of Xampp and copied the database over. All good so far...

There is also a batch script that runs which pulls info from a number of text files into the DB which runs in an infinite loop. Every 5 loops, the script calls an data history update query which basically adds 72 records to a large history table. This is done with 72 calls to a stored procedure.

The problem is, the history update takes approx 40secs to complete on the old server but 40mins on the new one. I've spent 4 days trying every possible setting I could think of to improve the performance and even tried to replicate the my.ini on the old server to see what happened but the same values are not improving the performance.

It's not a hardware issue because the new server is better than the old one so I'm stumped at this stage...

Anyone know what I can do to try and trace the issue or what I can do to improve the scenario?

All tables are MyISAM on the old server and I tried converting to InnoDB but the performance was even worse that way...

Old Server:

MySQL version: 5.5.8-log

New server:

MySQL version: 5.6.20

SHOW VARIABLES http://pastebin.com/AZFvTkQ5

SHOW STATUS http://pastebin.com/Ms9NxrrB


texts: PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted

texts:
I am looking to insert 1 to 8 sub-string values from an inserted string value into other fields as integers using a trigger in PostgreSQL. The integer values have a string prefix to identify them within the string value. A specific solution using my details below or a generalized example that helps me see how it can be done would be great. I want to do it this way (getting the integers from the 'calc_form' field string value) for control as mentioned in the "Discarding 3NF With Triggers" section of this link:
http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

The extent of my knowledge on triggers can be seen here (I am new to it):
http://www.w3resource.com/PostgreSQL/postgresql-triggers.php

Here are the real life details, which may not be necessary to some. A solution that does everything I need below is not necessary.

The string values are identified/prefixed with bp or wc before the integer I want to insert with the trigger. Where the bp values go in the bbg_pulls_[#]_id fields and the wc go in 'wh_calc_[#]_id` fields.

Everything happens within this table:

CREATE TABLE wh_calc
(
  id serial NOT NULL,
  calc_form character varying(500) NOT NULL,
  bbg_pulls_1_id integer,
  bbg_pulls_2_id integer,
  bbg_pulls_3_id integer,
  bbg_pulls_4_id integer,
  bbg_pulls_5_id integer,
  bbg_pulls_6_id integer,
  bbg_pulls_7_id integer,
  bbg_pulls_8_id integer,
  wh_calc_1_id integer,
  wh_calc_2_id integer,
  wh_calc_3_id integer,
  wh_calc_4_id integer,
  wh_calc_5_id integer,
  wh_calc_6_id integer,
  wh_calc_7_id integer,
  wh_calc_8_id integer,
  CONSTRAINT wh_calc_pkey PRIMARY KEY (id),
  CONSTRAINT wh_calc_calc_form_key UNIQUE (calc_form)
)

For example, this insert:

insert into wh_calc
(calc_form)
values
('[bp20]/[bp47140]');

...should yield the same result as this insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,bbg_pulls_2_id)
values
('[bp20]/[bp47140]',20,47140);

Here is another, slightly more complicated example. This insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]');

...should yield the same result as:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]',1,66,100);

Note, that in both examples I want to insert without the integer values and have the integers show up like the inserts with the integers. The inserts with the integers are only there to illustrate the result I want.


texts: SMSS 2014 Network Server browser is empty

texts:
I trying to setup a MSSQL 2014 environment and I installed SQL Server Management tools for a dedicated management computer to reach database server nodes easily without starting this resource-hungry stuff on servers itself.

However, on the management node, if i click on "Browse more..." in server address dropdown and i click on "Network Servers" tab, the listing is empty.
Checking it from a database server node, the tab filled correctly.

What things have to running correctly to be SMSS able to populate the listing of the servers (and instances) in the local network?

All computers are a part of the AD domain, the management node is one of the spare DC's (the machine has no other role than being DC, and only SQL Server management tools are installed), the two other node are a member server.

Windows Firewall set to allow any incoming conection from Domain zone, and adapters are configured to become a member of Domain zone, so i think firewall cannot be a problem.


texts: Does the row locking on slave database also apply to master database?

texts:
In a transaction, for example,

START TRANSACTION;
SELECT count(*) as count FROM `order` WHERE user_id = 25286 LOCK IN SHARE MODE;
INSERT INTO `order` (`id`, `user_id`, `product_id`) VALUES (NULL, '25286', '36296');

we perform the SELECT LOCK IN SHARE MODE; the SELECT query will be executed on the slave database and lock a row.

Does the SELECT LOCK IN SHARE MODE also create a row locking on the master database so that the INSERT query will not run on a master database?


texts:
I have a query in which I am updating a table and have used IN keyword in the query. The query is something like this:

UPDATE q
SET b_id = 123
WHERE id IN (SELECT id FROM q WHERE b_id IS NULL LIMIT 1)

There is another query in which I am inserting data in table. The query is:

INSERT INTO q (id, item) VALUES (@id, @item)

The queries are executing on different threads. While executing the second (INSERT) query I am getting database is locked error.

What is the reason?

id is not primary key.


texts: How can I make multiple joins on the same table?

texts:
I have a table (signal) which contains references to two other tables (ee and eeav).
I want to perform an SQL Query which combines the data from ee and eeav tables.

The situation is as follows:
The table signal can either have a reference to ee or eeav. In other words: when ee has a non-null value, eeav has the value null, and vice versa.
The table eeav has a reference to an ee.

These are the tables:

Table: Signal

+-----------+------+-------+
| s         |      |       |
+-----------+------+-------+
| s.id      | s.ee | s.eeav|
| 1         | 20   | null  |
| 2         | null | 500   |
| 3         | 40   | null  |
| 4         | null | 700   |
+-----------+------+-------+

Table: EquipmentEntity

+-------+---------+
|  ee   |         |
+-------+---------+
| ee.id | ee.name |
| 20    | ee20    |
| 30    | ee30    |
| 40    | ee40    |
| 50    | ee50    |
| 100   | ee100   |
| 101   | ee101   |
| 102   | ee102   |
| 103   | ee103   |
+-------+---------+

Table: EquipmentEntityActualValue

+---------+-----------+---------+
|  eeav   |           |         |
+---------+-----------+---------+
| eeav.id | eeav.name | eeav.ee |
| 400     | eeav400   | 100     |
| 500     | eeav500   | 101     |
| 600     | eeav600   | 102     |
| 700     | eeav700   | 103     |
+---------+-----------+---------+

Desired result

+----------+-----------+---------+
| s.id     | eeav.name | ee.name |
+----------+-----------+---------+
| 1        | null      | ee20    |
| 2        | eeav500   | ee101   |
| 3        | null      | ee40    |
| 4        | eeav700   | ee103   |
+----------+-----------+---------+

I am able to combine the signal table with the eeav table (including the ee) as follows:

SELECT s.Id, eeav.Name, ee.Name FROM signal s
INNER JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
INNER JOIN equipmententity ee ON (eeav.Ee = ee.Id)

I also managed to combine the signal table with the ee table:

SELECT s.Id, ee.Name FROM signal s
INNER JOIN equipmententity ee ON (s.Ee = ee.Id)

How can I "combine" these two results, so that I get the result as visualized in the "desired result" table?


texts: What is the meaning of different roles available(DB Admin etc.)

texts:
I have recently installed MySQL and also created the basic user as scott but my doubt is regarding the roles. I don't understand different roles available for the user which I have created.

I want the user to simply create tables, must be able to perform delete and update operations but restrict him from able to change the password and view the complete database available (all the records).
Based on my above requirement which kind of role should be given to scott?


texts: Postgres group records by consecutive types


texts: USING to join multiple tables with same field

texts:
I have 4 tables, each with a common field QuestionnaireId.

I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying the ON QuestionnaireId relations between each join.

Case A:

Select * FROM
    StudyQuestionnaire
    INNER JOIN QuestionnaireScore USING (QuestionnaireScoreId)
    INNER JOIN Questionnaire USING (QuestionnaireScoreId)
    INNER JOIN QuestionnaireScoreTags USING (QuestionnaireScoreId);

Case B:

Select * FROM
    StudyQuestionnaire SQ
    INNER JOIN QuestionnaireScore QS ON SQ.QuestionnaireScoreId = QS.QuestionnaireScore
    INNER JOIN Questionnaire Q ON QS.QuestionnaireScoreId = Q.QuestionnaireScoreId
    INNER JOIN QuestionnaireScoreTags QST ON Q.QuestionnaireScoreId = QST.QuestionnaireScoreId;


texts: How to query Mirroring RTO & RPO from system objects

texts:
Forward Clarification:  I'm using RPO to describe "Estimated Data Loss", and RTO to describe "Estimated Recovery Time" in this question. I realize they're not exact definitions, but are close enough for the sake of the question.

At the bottom of this article here, there's an excellent block of code used to calculate RPO for a given Always-On enabled instance:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b3177c3d-5450-4948-a234-34a8dd41bf37/estimateddataloss?forum=sqlreplication

--@EstimatedDataLoss (RPO)
WITH DR_CTE ( replica_server_name, database_name, last_commit_time)
AS
(
select ar.replica_server_name, database_name, rs.last_commit_time
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
where replica_server_name != @@servername
)
select ar.replica_server_name, dcs.database_name, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss, DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
inner join DR_CTE on DR_CTE.database_name = dcs.database_name
where ar.replica_server_name = @@servername
order by lag_in_seconds desc

And with a few of my own modifications, a similar query can be derived for RTO:

 --@EstimatedRecoveryTime (RTO)
select ar.replica_server_name, dcs.database_name, rs.redo_queue_size, rs.redo_rate, rs.redo_queue_size/rs.redo_rate as TRedo
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
where ar.replica_server_name != @@servername

These metrics will work fine for my Always-On environments, however I'm looking to apply the same for SQL Mirroring on some pre-2012 environments.

Are there similar system objects to calculate the same metrics for SQL Mirroring?

Note: My references to what counts as RTO and RPO come from this Mircosoft article:

https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx


texts:
Is it possible to have MySQL log queries or transactions where lock waits occur? One can debug specific application errors generated from lock wait timeout errors from the DB but without the ability to become aware of the occurrences of lock waits that don't hit the timeout, it seems difficult to anticipate where the next timeout error may be lurking.

Is there a way to have MySQL log these?


texts: Access denied when trying to use Mysqlimport Utility (using Root Credentials)

texts:
We are trying to import large CSV files through Mysqlimport as described in This & This article. But even though I am using root credentials I keep getting the following error:

  mysqlimport: Error: 1045 Access denied for user 'root'@'localhost'
  (using password: YES)

Below is the command I have used:

mysqlimport --ignore-lines= --fields-terminated-by=, --columns='company, company_addr,
city, zipcode, state, phone, fax, email, website, contact_name' --local -u
 root -p dss_databank /data/brick1/web/web/data_files/test_ssh_insert.csv


texts: How to get the records from Amazon Athena for past week only

texts:
I am writing a query to get Amazon Athena records for the past one week only. Here is what I wrote so far:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin';

But I am not sure how to write it to extract records for the past 1 week only.


texts:
I need to implement a DB of tracking numbers. The tracking numbers table could potentially contain billions of records during its lifetime so an archive database should be implemented to improve future performance and maintenance costs.

I'm thinking of prepending the year in the tracking number, say 2019-XXX for all tracking numbers in the year 2019, then 2020-XXX for the year 2020 and so on.

The tracking numbers are then stored on their respective year table e.g., TrackingNumbers_2019, TrackingNumbers_2020, and so on.

Every new year, the previous year's table will be dropped from the "active" DB then moved to an "archive" DB. The archive DB will be rarely queried. It will be on a lower-end machine with read-only mode. It will contain all previous years data.

Now in case somebody wants to search for a specific tracking number, the application will determine which DB/table it's going to look on based from the year prefix. E.g., If the current year is 2019, then a search for 2019-XXX will lookup the active DB, but a search for 2018-XXX will lookup the archive db.

Do you think this design is sound or is there a better way?


texts: encrypt data without performance issues?

texts:
I've googled around for the last 45 minutes or so and it seems like that I can't figure out what kind of options I got when it comes up to encryption in databases.

Problem:

I got a SaaS product and the data is kinda sensitive.
I want to give my customers 100% security on the fact that I can't read their data.

Is this even possible?
What kind of possibilities do I got?


texts: How does one create a notification upon corruption being found during Automatic Page Repair under database mirroring?

texts:
I read:  https://msdn.microsoft.com/en-us/library/bb677167(v=sql.120).aspx#UnrepairablePageTypes

But, I am unclear how I can set up a notification so I am aware when this occurs.

Any advice?


texts: I have 2 queries with only one difference that I want to merge in just one query

texts:
I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two.

I believe that the combination of the two will give me again of significant disengagement and less rework.

My goal is to know the "valor_bruto_atual" from the first day and the last day.

First query:

SELECT
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT
            MAX(ID) as max_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

Second query:

SELECT
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_min,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT
            MIN(ID) as min_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

The only change was the fancy name of the column and the min() or max() function inside the where condition.


texts: Can i alter a database to single user which is configured in log shipping

texts:
We are using a production database (SQL Server 2012) and database compatibility mode is 90 (SQL Server 2005).

Can I alter the database to single user mode and change to 110?
What will be the impact if run Alter database <DBNAME> SET SINGLE_USER with rollback immediate
After changing the compatibility level and switching to multi user will Log shipping work?


texts: Exporting certificate without private key

texts:
I am using the following PowerShell code to create a certificate that can be used as Column Master Key for Always Encrypted feature:

New-SelfSignedCertificate -Subject "AlwaysEncryptedSecurityLevelNormalV001" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 -NotAfter (Get-Date).AddYears(100)

Then I export the certificate and import it into the machine where the SQL Server is running. During the export I am disabling the option to export the certificate private key. So, if one try to export it, he/she will get the following:

This is nice because the certificate private key is protected for export.

I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?


texts: Why tmp tables created when connected to one database

texts:
I am wondering why temp tables created while switching to database. When I login to the mysql server, I have queried current session temp tables as follows:

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

3 rows in set (0.00 sec)

mysql> \u database_name
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show status like '%tmp%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Any idea why were 3 temp tables created ?


texts: List of nodes types in a query plan in PostgreSQL?

texts: Where can I find an exhaustive list of possible nodes types(e.g. Seq scan, groupAggregate, Nested loop ...) that a query plan could contain in PostgreSQL?


texts: Disabling Resource Governor in SQL Server

texts:
Should we restart SQL Server after disabling Resource Governor? What is the best practice?

Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?

For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?


texts: How does SQL Server reduce index fragmentation?

texts:
If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes?

MSDN suggests that if an index is over 30% fragmented, it is recommended to run REBUILD instead of REORGANIZE. Would running REORGANIZE multiple times do the same things as REBUILD?

I wonder about this because I have a client that has a highly fragmented index. They run REORGANIZE against that index every weekend, and over time it seems like their index becomes defragmented.

Does this make sense?


texts: What is the "Optimize Response" connection string attribute used for?

texts:
My apologies if this is not the correct forum to be asking this in.

I am having trouble finding information regarding the "Optimize Response" connection string attribute. The most promising source of information I have found is here, but it doesn't really make clear to me what the settings are, or how it will really impact an MDX query on the server side.

There are two cases for optimization? One is to use the "NormalTupleSet", which I can't even find what that is anywhere. The other is for when the slicers are empty. What do each of these really do? Would a client use the second option for empty slicers, when they are instead passing a subquery to the FROM clause?

Thank you for any help anyone can give me in this regard, especially if you can point me to accessible resources on the topic.


texts: Using Azure MSSQL "contained database" how do you force a user to reset their password?

texts:
I'm setting up new users in an Azure MSSQL Contained Database.

I want new users to be forced to reset their password on the first login.

I see the ability to set a new password documented https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver15 but I do not see the ability to force the reset.

Any ideas?


texts:
For some reason my SELECT query with an AND clause is exhibiting some odd behaviour. It returns zero rows, when I know for a fact that there are matches. If I remove either of the clauses, it works. Or, the strangest part, if I add a wildcard to the end of the string to match, it returns the expected results. Even though there doesn't appear to be any extra bytes at the end of the column data.

A demonstration:

SELECT HEX(type),
       type,
       category
  FROM cats
 WHERE `category` = 10 AND
       `type` LIKE "Furry";

Empty set (0.07 sec)

but

SELECT HEX(type),type,category FROM cats WHERE `type` LIKE "Furry";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...

And

SELECT HEX(type),type,category FROM cats WHERE `category`=10 AND `type` LIKE "Furry%";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...

What am I missing here?


texts:
I was cleaning out some files in /home/postgres, and accidentally deleted the directory named 10.  Now nothing works.  Here is the error I get when I try to run psql as the postgres user:
    psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What did I break, and how do I fix it?
Other info:
-This is a scratch database, so I am not very concerned about recovering old stored databases.  However, it would be easier for me if I could.
-I thought about doing an uninstall/reinstall using apt-get, but since things are in an unstable state, I thought it would be better to get some advice first.
-I am running postgres 10 on Ubuntu 18


texts: Database design: where do I put this data?

texts:
EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

This is what I've got now. However, events of some event category type need extra data fields. So where do these fields go? This is what I'm thinking...

EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

SpecialEvent
============
EventId PK,
Extrafield

So there's a 1-1 relationship between Event and Special Event (this structure will map to a single SpecialEvent class in C#) and all events and only those events that reference the pertinent category will have a row in this new table.

Is this a fair design? My qualm is that an event will have a a corresponding row in the SpecialEvent table and yet won't reference the required event category or vica versa.


texts: I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?


texts: Is there a way to install both tabular and multidimensional modes?

texts:
I have started studying for the MCSE BI exam. I would like to have both the multidimensional and tabular modes available for studying/practice purposes. I have a SQL Server 2014 Developer Edition with tabular mode installed.

Is it simply a matter of installing another named instance? Seems like there's a gotcha somewhere in there, but I have not found it in the research I have done so far.


texts:
I have installed IBM DB2 10.5.5 and I want to create a new database there.

When I entered command

db2 create database test1

I am getting a error saying

SQL1084C  The database manager failed to allocate shared memory because an
operating system kernel memory limit has been reached.  SQLSTATE=57019

Following is the output I get when I run "ipcs -l"

 ------ Shared Memory Limits --------
max number of segments = 10000
max seg size (kbytes) = 4882812
max total shared memory (kbytes) = 33554432
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 4096
max semaphores per array = 250
max semaphores system wide = 1024000
max ops per semop call = 32
semaphore max value = 32767

------ Messages Limits --------
max queues system wide = 16384
max size of message (bytes) = 65536
default max size of queue (bytes) = 16384

What is the reason for this? How can I fix it?
I am running on Ubuntu 14.04 with 8GB physical memory.


texts: How to repair corruption for "Possible chain linkage problem."

texts:
I have large table with 15 000 000 record and suddenly Select Top stopped working. I use MS SQL Management Studio.
This is not working, the query return 0 records:
SELECT TOP (10)
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]

To my surprise if I try to select one ID field it works percently.
Working fine:
SELECT TOP (10)
       [Id]
  FROM [ca-v2].[dbo].[Log]

Also if I add order by at the end the query starts working too:
SELECT TOP (10)
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]
  Order By [DateStamp]

I ran DBCC CHECKTABLE on the table, and received the following error:

Error: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1029578706, index ID 1, partition ID 72057594043367424, alloc unit ID 72057594045071360 (type In-row data). Page (1:5044) is missing a reference from previous page (1:5042). Possible chain linkage problem.

What can I do about this?
With the help of @RDFozz I was able to repair damaged index using sql
ALTER DATABASE "db" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

DBCC CheckTable ("log", REPAIR_REBUILD)

go

ALTER DATABASE "db" SET MULTI_USER WITH ROLLBACK IMMEDIATE

Two important thing here: 1) a database must be set to single user mode. 2) if database is in production and has other connections the SET user command must got with ROLLBACK IMMEDIATE to drop current connections.


texts: SQL Developer cannot connect to Oracle database, yet sqlplus can!

texts:
I am having a problem connecting sql developer to my Oracle 10g database.  sqlplus is able to connect fine, but when I try to open the connection in sql developer, I get 'ORA-12514 TNS does not know of service' error.  I have tried modifying my tnsnames.ora file.  It has the following:

COTT3 =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVER_NAME = COTT3)
  )
)

Can anyone assist with any other files I may need to modify or let me know where im going wrong?

Thanks

UPDATE
Output of lsnrctl status

 [oracle@VM-COT-DEV1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2012 13:57:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                02-FEB-2012 13:01:25
Uptime                    0 days 0 hr. 55 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

enter code here


texts: Resolving Cyclical Reference in Referential Relationships

texts:
I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an account/password manager.

Here are my relationships:

Now, I'm attempting to set CASCADE delete rules like so:

category.id ---> account.category_id
account.id ---> credential.account_id
category.id ---> field.category_id
field.id ---> credential.field_id

After adding the first 3 relationships, I get an error while adding the 4th (field ---> credential):

The referential relationship will result in a cyclical reference that is not allowed.

I'm struggling to determine why this is happening and how to fix it. Any ideas?


texts: Installing instances of older versions of SQL Server

texts:
Are there any issues to be aware of when installing an instance of previous version of SQL Server AFTER installation of a newer version? e.g. Installing 2008 to a server with an existing 2012 instance?

I can find not statements specific to that circumstance, but it seems risky.


texts:
I've created the following script to pull up duplicate indexes on my DB but I need to find a way to implement it across all the databases on the SQL server - any suggestions?`

with indexcols as
(

select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as "index",
c2.name as "exactduplicate"
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;


texts:
how to fix it I am new in Oracle?

I tried to connect RMAN and it showed me this error...


texts: Every "INSERT" query check a combination of column's records

texts:
Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema.

What i want?

In a simple table, for instance Table1, there are two different columns, Series and Orders.

I would like every new record for Orders be unique for Series.

  Series: A Orders: 1,2,3,4,5,6

  Series: B Orders: 1,2,3,4,5,6

If the user try to insert Series A and Orders 1 for second time i would like to have an error.

I think that the best way is using stored procedure? Am I right?

Thanks in advance.


texts: Allow databases start with specific name

texts:
In PostgreSQL sometimes we replace a database, we rename the old when a create a new one, example:

Database: test
Rename database to: test-04-04 (date)
Create a newdatabase: test

Now we have a lot of databases named : test-"date" and its becoming hard to add database at pg_hba every time, is there a way to allow all database start with test like test* ?


texts: Server response time high when InnoDB tables active but not being accessed

texts:
I am experiencing high server response time (up to 5 seconds) when loading every Webpage on my server, ONLY when MySQL InnoDB tables are running.

The 5 second delay occurs even on static HTML pages with no call to any MySQL data table (and no other users on the site accessing other InnoDB linked webpages).

As soon as the InnoDB is shutdown (MyISAM tables remain active), all pages (those that call DB or not) load very fast (including MyISAM tables) in about a second.

How could InnoDB be causing server wide slow response, even when not being accessed?


texts: Can I create an AlwaysOn replication of a DB where some tables don't have a primary key?

texts:
Using SQL Server 2016 and I have a 3rd party application that uses a large database on my SQL Server for data storage, processing, everything.

I have a BI team that needs access to this data to run reporting and batch-processing tasks, some of which include heavy queries that bring the server to its knees, so they can't be doing that on the live DB. To that end, I have an existing log-shipping solution which maintains a replication of this DB for them to use, but the amount of data and number of users who depend on data from that replication has grown to the point where taking the DB offline several times during the day to perform a log-shipping update is no longer acceptable.

So, I want to use an AlwaysOn availability group replication instead of log-shipping, so that the data remains online during business hours and is automatically updated.

However, my problem is that the application whose database I'm replicating is a little old and out of date, and whoever designed it clearly didn't know or care much about database optimization, since a lot of the larger tables don't have a primary key.

I've added indexes to some of the tables, which has worked OK, but if I modify the structure of the tables at all (such as adding a primary key), the application refuses to recognize them as valid and stops working. So these tables are going to have to remain without primary keys until I can convince the vendor to add them, which is likely to take years.

In the meantime, Is there anything I can do? I've tried using transactional replication and it's replicated all the tables withprimary keys just fine, but the tables without keys simply aren't there in the replicated DB.

I've heard that merge replication can be done on tables without primary keys, and it's possible to use merge replication for availability groups. Is that true? If it is, can you point me in the right direction to create such a replication?


texts: It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL?

texts:
I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime.

I don't expect the past data from select to change overtime, the select will just return more new data, and the same old data of previous queries.

But I cannot insert old data again because of the unique key constraint on the primary keys.

What I would like to achieve is a kind of INSERT ...ON DUPLICATE KEY UPDATE... but for a INSERT...SELECT.
Skipping the duplicate keys, will be enough, but I'm not sure how to do it


texts: Optimizing one-way syncing of large (wide) tree

texts:
I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes.

At the moment, the trees are stored using simple parent_id FKs.

A tree is created/generated in a program, and then it needs to be saved in the database. The catch is that we cannot just wipe the previous tree (of the same type, if one exists), we need to update it to reflect the new (changed) tree. Occasionally this means swapping the entire tree (if the root changed), but a lot of the time it just means minor changes here and there (e.g. add some children/sub-trees, retire others). We also do not delete old children (and their children), they just get marked as old.

The way updates/verifications are done is by traversing through the tree (depth-first) and checking each node one-by-one.

One of the performance issues is that it can take almost 2 minutes to just verify that a tree (~50k nodes) in the db is the same as the one in the program (since it involves n selects where n is the number of total nodes).

While utilizing an ltree structure could be beneficial in terms of reads, I don't think it would solve this issue (inserting/updating/verifying a tree).

I'm not sure what the best way to optimize this is. One idea I had was to calculate and store a hash of every sub-tree, and that way it could potentially speed things up in most cases since it would know what parts of the tree that doesn't need touching. The hash calculation should be pretty fast and can be parallelized in the program, where the workload is easier to manage. However, perhaps there is some other way that I'm missing?

Bulk inserts etc. are impossible since the tree relies on recursive relationships.

Furthermore, the database is naturally in the cloud, which means that these thousands of queries being executed one-by-one probably incur quite a bit of time just in terms of latency (both the program and the db are on AWS so the latency is small, but still exists of course). A part of me wants to bulk insert the newly generated tree into some temporary table and then write a DB function that syncs it into the actual table.

General structure/schema of a tree:

Each node has an id and a parent_id column (nullable FK to id)
The root node has parent_id=NULL
What makes each node unique is (parent_id, attrs...)

Meaning that duplicate nodes can exist in multiple places in the tree

Nodes are only added or marked as obsolete

Any advice is much appreciated!


texts:
I am trying to bring up a postgres server where the configuration files and the data are in separate places/partitions

postgresql-10 has been installed using the official repository using YUM in Centos 7

And it has been initialised using

/usr/pgsql-10/bin/pg_ctl initdb --pgdata=/var/lib/pgsql/10/data

as user app

What is referenced as PGDATA (configuration) directory is the default one:

# ls -salt /var/lib/pgsql/10/data
total 120
 4 drwxrwxr-x. 19 app app  4096 Nov 10 17:29 .

I would like to use the following directory for data storage:

# ls -salt /usr/ip-spotlight/postgresql/
total 12
4 drwxrwxr-x. 3 postgres  postgres  4096 Nov 10 17:43 .
4 drwx------. 2 postgres  postgres  4096 Nov 10 17:43 data

And it complains about:

2017-11-10 17:51:44.495 CET [81743] FATAL:  "/usr/ip-spotlight/postgresql/data" is not a valid data directory
2017-11-10 17:51:44.495 CET [81743] DETAIL:  File "/usr/ip-spotlight/postgresql/data/PG_VERSION" is missing.

On the other hand, in https://www.postgresql.org/docs/current/static/runtime-config-file-locations.html it says:

  If you wish, you can specify the configuration file names and
  locations individually using the parameters config_file, hba_file
  and/or ident_file. config_file can only be specified on the postgres
  command line, but the others can be set within the main configuration
  file.

My /var/lib/pgsql/10/data/postgresql.conf is configured as:

data_directory = '/usr/ip-spotlight/postgresql/data'
hba_file = '/var/lib/pgsql/10/data/pg_hba.conf'
ident_file = '/var/lib/pgsql/10/data/pg_ident.conf'

Could you please advise how to separate the config files of postgresql and the actual data storage ?


texts: CREATE PLUGGABLE DATABASE REFRESH MODE MANUAL from remote fails with ORA-00922

texts:
I'm doing some test with remote PDB cloning. Both local and remote instance are

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

The DB LINK is working, and the remote PDB exists

SELECT COUNT(*) FROM V$PDBS@REMOTE
WHERE NAME = 'REMOTEPDB';

But doing this on sqldeveloper

CREATE PLUGGABLE DATABASE mypdb FROM REMOTEPDB@REMOTE
  REFRESH MODE MANUAL;

Gave me this error:

Error starting at line : 1 in command -
CREATE PLUGGABLE DATABASE mypdb FROM REMOTEPDB@REMOTE
  REFRESH MODE MANUAL
Error report -
ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:
*Action:


texts: Why isn't my !include directive working in MySQL?

texts:
I am experimenting with option files in MySQL.  I have a ~/.my.cnf file which includes only the following:
!include /Users/myself/Workspace/project_time/mysql/foo.cnf

[client]
i_am_a_dummy

And I have a file /Users/myself/Workspace/project_time/mysql/foo.cnf which includes only the following:
[mysqld]
skip_column_names

I have newlines at the end of each file.
I also have a database, new_database, which includes a table t1.
I run MySQL with the command mysql -uroot -p new_database; and type in my password at the prompt, after which I see the MySQL prompt.  When I type select * from t1; at the prompt, I would expect to see the output without headers, like so:
mysql> select * from t1;
+------+------+------+
| 2001 | 1    | 1    |
| 2001 | 1    | 20   |
| 2001 | 1    | 30   |
| 2001 | 2    | 2    |
| 2001 | 2    | 23   |
| 2001 | 2    | 23   |
+------+------+------+
6 rows in set (0.00 sec)

Unfortunately, instead I see the following:
mysql> select * from t1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2001 |     1 |    1 |
| 2001 |     1 |   20 |
| 2001 |     1 |   30 |
| 2001 |     2 |    2 |
| 2001 |     2 |   23 |
| 2001 |     2 |   23 |
+------+-------+------+
6 rows in set (0.00 sec)

I have already tried to use [client] as the option group in foo.cnf instead of [mysqld], but that did not work.
When I add skip_column_names directly to ~/.my.cnf (just below i_am_a_dummy), it works as expected.
What am I missing?


texts: Using dynamic sql inside Oracle stored procedure

texts:
Assuming I have the following procedure

CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type,
                     table1_val IN TABLE1.table1_value%type)
AS
  SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2';
BEGIN
  --.....
  --1 :
    EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id;
  --2 :
    UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id;

END;

Beside the style/readability, is it any performance penalty for using dynamic query (1) compared to (2)  in such cases (I mean when it's absolutely avoidable) ?

Thank you.


texts: Changing Databases Dynamically using Variables

texts:
I want change into a database programatically using in code:

DECLARE @SQL nvarchar(2000)
DECLARE @DBName sysname

SET @DBName= 'foo'
SET @SQL = 'USE [' + @DBName + ']'

select @SQL
execute @SQL

When I execute the @SQL I get this error:

  Msg 203, Level 16, State 2, Line 9
  The name 'USE [foo]' is not a valid identifier.

Is there a way to do this? Thank you in advance.

The goal is to now be in the foo database after this runs.


texts: How to identify all WALs created during start_backup and stop_backup?

texts:
I'm implementing incremental backups using filesystem-level snapshots. pg_start_backup() and pg_stop_backup() can be used to this end. Via these pair of functions or via the backup_label file, I can identify the "START WAL LOCATION" and "STOP WAL LOCATION" and associated files. The problem is, when these files are not the same and not strictly consecutive (ie, there are intervening files), how do I deterministically determine them? While it might be the case that the files are monotonically increasing, how can I be sure that is the case, and how can I be sure I've located all the intervening files? Can I be guaranteed the files will be written or changed in chronological order? Or in lexicographical order? (From what I've seen, neither is the case.)

After 3x reading the documentation, I can't get any clarity or resolution on this: it tells us that the backup process must include these files, but offers no indication on how to identify them! If I've overlooked a page in the documentation, I'd appreciate being it pointed out.

BTW: Please don't tell me to use pg_basebackup unless you want a kick in the face.

BTW2: I'm trying to make a complete backup set without doing a full backup. Barman stores the archive logs somewhere else, which is what I want to avoid. Even if I implemented barman (which I don't want to), I'd still need to know which files I need for the backup during its operation.


texts: Using multiple Active Directory accounts to connect to SQL Server

texts:
I have web applications (Classic ASP and .NET) that connect to SQL Server using SQL authentication. Each one has their own login. I want to convert these to Windows Authentication. The new AD users (1 per application) have already been established for each application.

What is the most secure way to use these AD users? Should I configure each application's application pool in IIS to run under the new AD user account, and then just use the "integrated security" option in the connection string from each application?


texts: SQL Server remote connection to 2nd instance impossible

texts:
After installation of another SQL Server 2014 instance to a server where a default instance of SQL Server 2008 R2 is present, I cannot remote connect remote to the new named instance.

The default 2008 R2 instance uses TCP port 1433 set for all "IP(n)" settings.

Here is what I read / checked / tried:

Following this question: configure remote connection for multiple instances sql server 2008

I figured several settings and tried the following:

Server Security: Mixed
Server Connections Properties: Allow [checked]
SQL Server Browser started
Protocol Settings for the 2014 instance set as follows:

Shared Memory=Enabled,
Named Pipes=Disabled,
TCP/IP=Enabled

TCP IP Setting of new instance:

Enabled=Yes,
Listen All=YES,

TCP/IP Settings for IP(n):

Dynamic Ports=erased
TCP Port=erased

IPAll Setting:

Dynamic Ports = erased
Port=49172

I tried to remote connect using [IP]:49172\Instance and HostName:49172\Instance and HostName\Instance and [IP]\Instance without success.

I try to use SQL Server authentication, the user is available and is sysadmin and can connect local.

What am I missing here?


texts: Receiving several deadlocks with simple queries when ran concurrently from different nodes

texts:
We have a web application with three nodes that sit behind a load balancer that handles traffic in a round robin fashion (no sticky sessions). When we receive several requests coming in at the same time we will start to see several deadlocks occurring on one of our queries that obtains a session id. We have a table called uniqueids that we populate with around 250,000 rows that contains sequential ids specific to our application. We use the stored procedure getnextsessionid to select the next unused id and then update the used column in that row to show it has been used. Is there a different locking method we can use to ensure no two people receive the same id and prevent deadlocks?

ALTER    PROCEDURE [dbo].[getnextsessionID]

    (
    @id varchar(15) OUT
    )

AS

    SET NOCOUNT ON
    SET LOCK_TIMEOUT 8000

    BEGIN TRAN
        SELECT TOP 1 @id = RTRIM(LTRIM(sessionID))
        FROM uniqueids WITH (HOLDLOCK,ROWLOCK,INDEX = session_used)
        WHERE used = 0

        UPDATE uniqueids
        SET used = 1,dt = GetDate()
        WHERE sessionID = @id

    COMMIT TRAN

--uniqueids table

    CREATE TABLE [dbo].[uniqueids](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [sessionID] [varchar](15) NOT NULL,
        [used] [bit] NOT NULL,
        [dt] [smalldatetime] NULL,
     CONSTRAINT [PK_uniqueids_id] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY],
     CONSTRAINT [U_sessionID] UNIQUE NONCLUSTERED
    (
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [session_used] ON [dbo].[uniqueids]
    (
        [used] ASC,
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[uniqueids] ADD  CONSTRAINT [DF_uniqueids_used1]  DEFAULT ((0)) FOR [used]


texts:
We are running a pretty popular application in Russia and other CIS countries through a pretty old accounting system called BEST. It's all built on FoxPro (not Visual Foxpro) DBF/CDX tables. It's open but it doesn't have API for interacting with other software. So we have to use direct access to the tables and data.

We use Sybase Advantage Database Server (ADS Internet Connector) to connect our online store to BEST. During developing, testing and running it for the first three months everything was fine. But almost half year ago we started to get index corruption errors. We tried almost everything - replaced network cables and NICs, RAID controller with HDDs, memory, reinstalled windows server. We rebuilt the tables and indexes, looked into the logs - but everything is useless. Once in two days we get index corruption error, so we have to stop the server, reindex tables and start it again and the whole company is waiting for 10 minutes.

We have problems with only 2 tables - orders and their contents. All other 300 tables that are used are never harmed. One more problem that is making the case harder is that the problem is not immediate. When the index file is broken - it's not seen. Users continue to work, until one of them starts a new session BEST or a client makes an order. So it's impossible to catch the moment when it happenes.

Right now we are blaming ADS. Does anyone know about such problems and their solutions in ADS. I've surfed all the internet looking for answers, but found none.

Thanks, if you know something.


texts:
Here are my tables:

accounts_company:
 - id
 ...

accounts_companyview:
 - account_id: FK to company
 - viewer_id: FK to company

shipments_address:
 - companyview_id: FK to companyview
 - is_shipper: boolean (not null)

accounts_similarcompanyview
 - original_id: FK to companyview
 - similar_id: FK to companyview

This is my query (generated by Django)

SELECT COUNT(*) AS "__count"
  FROM "accounts_similarcompanyview"
 INNER JOIN "accounts_companyview"
    ON ("accounts_similarcompanyview"."original_id" = "accounts_companyview"."id")
 INNER JOIN "shipments_address"
    ON ("accounts_companyview"."id" = "shipments_address"."company_view_id")
 INNER JOIN "accounts_companyview" T5
    ON ("accounts_similarcompanyview"."similar_id" = T5."id")
 INNER JOIN "shipments_address" T6
    ON (T5."id" = T6."company_view_id")
 WHERE ("accounts_similarcompanyview"."are_similar" = true AND "shipments_address"."is_shipper" = true AND "accounts_companyview"."viewer_id" = 51729 AND T6."is_shipper" = true AND T5."viewer_id" = 51729);

Output of EXPLAIN ANALYZE: https://explain.depesz.com/s/slJK

I can see that there is an Index Scan taking most of the time, so I tried to add a few indexes but no change whatsoever, I still get a full index scan.

CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper");

CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper", "company_view_id");

What index could I add to make this query faster?

EDIT: forgot to add the size of the tables

select count(*) from shipments_address;
 2998765

select count(*) from accounts_company;
 168224

select count(*) from accounts_companyview;
 371560

select count(*) from accounts_similarcompanyview;
 83434


texts:
I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.


texts:
I have a weird problem I am struggling to troubleshoot.

I have a development server with 18GB RAM and two SQL Server 2012 SP3 instances with @@version output:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

As far as I can see from checking gpedit, Lock Pages in Memory is not enabled on the server.

Each instance has the following memory settings:

Min: 1024
Max: 7168

I'm getting performance issues with Instance 2. Slow queries etc. Instance 1 is fine.

To do a basic check for memory usage I'm running:

SELECT counter_name AS TheCounter
    ,cntr_value AS Value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
        'Total Server Memory (KB)'
        ,'Target Server Memory (KB)'
        )

If I run that query against Instance 1 I see this:

Target Server Memory (KB) - 7340032
Total Server Memory (KB) - 7340024

I'm happy with that.

But if I run the same against Instance 2, I see this:

Target Server Memory (KB) - 4349216
Total Server Memory (KB) - 878896

To me that means the instance wants 4GB and can only consume 878MB.

There are no other memory intensive processes running on this server. The output of dm_os_process_memory is as follows:

physical_memory_in_use_kb="589728"
large_page_allocations_kb="0"
locked_page_allocations_kb="0"
total_virtual_address_space_kb="137438953344" virtual_address_space_reserved_kb="20263356" virtual_address_space_committed_kb="693112" virtual_address_space_available_kb="137418689988"
page_fault_count="249811"
memory_utilization_percentage="100"
available_commit_limit_kb="6501636"
process_physical_memory_low="0"
process_virtual_memory_low="0"

Can anyone tell me why Instance 2 is not using the memory it has been allocated, and how best to troubleshoot?


texts: Is there an easy way to replicate MySQL data?

texts:
My software is deployed in various cities, each have a database, but they have different authorizations such as:

              central DB
                 /\
                /  \
              DB1  DB2
              /\    /\
             a  b  c  d

Databases a & b need replication to DB1.  Databases DB1 & DB2 need  replication to central DB.

How can I accomplish this?


texts:
I work with scientists who analyze pictures. These pictures have a limited number of colors and a main component of their analysis is to understand how particular pixels are distributed within each image. So basically what I need to save for a full analysis is the position and color of each of the pixels. The images that I need to process are things similar to these:

Note that the shapes are often not squares or rectangles, but could be any shape. However, what is always the case is that:

There is one background color that will dominate 60-90% of the image
There are only 6 different colors in use
Images vary in size from 100x100 pixels to 1500x1000 pixels

We will be processing hundreds of thousands of images. So, this will quickly becomes huge if I want to store every pixel's color (large images have 1.5 million pixels). I haven't dealt with databases of this size before, nor have I dealt with storing pixel data from images, so I was wondering if you can give any advice with regards to:

Database recommendations (favoring PostgreSQL but any open source solution can be considered)
Tricks to store the data without things becoming so huge

One thing I'm thinking is to store the main background color for each image, and that way I only have to store those pixels that are NOT the background color. It would help, but I wonder if there's more that can be done...


texts: mysql cannot start because of init.d folder?

texts:
After I setup mysql 5.5, I changed the data dir to another folder and modify the related config in my.cnf. This is quite simple, but mysql cannot start anymore even if reinstall or not change data dir.

The error log has no useful information, so I cannot find any reason.

  111207 16:28:02 mysqld_safe Starting mysqld daemon with databases from
  /var/lib/mysql 111207 16:28:02 mysqld_safe mysqld from pid file
  /var/lib/mysql/localhost.localdomain.pid ended

But if I use the command as the start script, mysql starts normally.

  /usr/bin/mysqld_safe --datadir=/data/mysql/
  --pid-file=/data/mysql/localhost.localdomain.pid > /dev/null 2>&1

Much more strange, I use /usr/share/mysql/mysql.server start instead of /etc/init.d/mysql start, mysql also can start without error, while these two files are the same. So in the end I have to use cp -l /usr/share/mysql/mysql.server /etc/init.d/mysql to fix the problem.

I really want to know why and if there is a better solution!


texts: Can't connect to Azure SQL with error 18456

texts:
I'm trying to grant access to my Azure SQL database (on an elastic pool) to an external developer using SQL Authentication.
In the database (not master) I ran the following commands:
create user [UserName] with password='Password', default_schema=[dbo]
alter role db_owner add member [UserName]
He can't access the database, he gets the error:
Login failed for user 'UserName'. (.Net SqlClient Data Provider) Error Number: 18456 Severity: 14 State: 1 Line Number: 65536
Both our IPs have been whitelisted, but only I can login to the database with those credentials. Any idea where things are going wrong?


texts: SQL Replication Errors: Replication 'publish' already been set to true and logreader agent already exists

texts:
I'm getting this error when trying to create replication:

  The replication option 'publish' of database 'servername' has already
  been set to true. [SQLSTATE 01000] (Message 14035)  The logreader agent already exists. Use 'sp_changelogreader_agent' to
  change any settings/properties. [SQLSTATE 42000] (Error 21831).  NOTE:
  The step was retried the requested number of times (5) without
  succeeding.  The step failed.

I used the wizard to generate the script, then I use the scripts in job steps.  I deleted the existing replicaiton and then reran the job that creates both the snapshot and the transaction replication and it gives me the same error.

I'm not sure if you need any more information, if so, just let me know.  Currently, I'm not sure what else to look into?

Thanks for your help.


texts:
I'm new to MySQL.  However I am very familiar with Oracle and MSSQL.  This is what I have done so far:

Installed MySQL 5.7 on RHEL 7.2
Created a test db called "mydb" and test table called "employee".
Location of these files are
/usr/var/lib/mydb
/usr/var/lib/mydb/employee.frm
/usr/var/lib/mydb/employee.idb
Able to connect to MySQL from a client machine and can see the "mydb".

So my server and connectivity are fine.  Now I want to move some of the MySQL 5.0 databases from an old Windows box to this new RHEL Linux box.

Sample of old MySQL 5.0 database files:

region.myd
region.myi
region.frm

I don't have much knowledge of the MySQL database architecture.  I just noticed the MySQL 5.0 has extensions of .frm, .myd and myi while the MySQL 5.7 has .frm and the .ibd

My question is: How can I quickly migrate those two "region" files to my new RHEL box so they conform to the .frm and .ibd file format?

Thank you


texts: After dropping the clustered index, it is still attempting to do a clustered insert

texts:
I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.

I am running the following script to do it:

USE [Cvti101687]
go
truncate table [IDAT_MR_NOTEHISTORY]
go
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_MR_NOTEHISTORY]') AND name = N'PK_iMR_NOTES')
    ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] DROP CONSTRAINT [PK_iMR_NOTES]
GO
USE [101687_test2]
GO

declare @ChunkCounter int
declare @ChunkSize int
set @ChunkCounter = 0
set @ChunkSize = 50000

--hack to do a do-while loop in sql
while 1 = 1
begin
    insert into cvti101639..IDAT_MR_NOTEHISTORY with (tablock)
    SELECT newid(), '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000'
    ,'00000000-0000-0000-0000-000000000000',    ISNULL(TargetClientAccountNum, [REC_CLINUM]), ISNULL(TargetPetAccountNum, '     ')
    ,ISNULL(pck_desc, 'Converted Medical Record'), isnull([REC_DOCTOR], '99999'), isnull([REC_ENTRY],''), isnull(REC_ENTRY,''),[rec_dattim], 0
    FROM [RECORD]
    left join [WVSSPCK] on pck_code = 99 and pck_link = [REC_TAG]
    left join cvti101639..idat_patients p on p.oldAccountNum = REC_CASE
    where rec_deleted = 0 and (rec_rtype = 0 or rec_rtype is null)
        and (TargetClientAccountNum is not null or [REC_CLINUM] is not null)
        and rec_id >= @ChunkCounter and rec_id < @ChunkCounter + @ChunkSize

    --If no rows inserted, break out of the loop
    if(@@rowcount = 0)
        break;

    set @ChunkCounter = @ChunkCounter + @ChunkSize
end

go
USE [Cvti101687]
go
ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] ADD  CONSTRAINT [PK_iMR_NOTES] PRIMARY KEY CLUSTERED
(
    [cvtGUID] ASC
)

To speed things up, I attempted to drop the clustered index on the destination table and rebuild it at the end (Yes, I know it's a clustered index on a GUID, I am just the conversions guy, I don't get a say in how the database is designed).

However, when looking at the actual execution plan from one of the iterations of the while loop of the insert, I see that it is performing a Clustered Index Insert on my index that I already dropped. Furthermore, the sort it is performing before the clustered index insert costs 69% of each chunk.

  |--Clustered Index Insert(OBJECT:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[PK_iMR_NOTES]), SET:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtGUID] = RaiseIfNull([Expr1012]),[Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtClientGUID] = [Expr1013],[Cvti
        |--Parallelism(Gather Streams, ORDER BY:([Expr1012] ASC))
             |--Sort(ORDER BY:([Expr1012] ASC))
                  |--Compute Scalar(DEFINE:([Expr1012]=newid(), [Expr1013]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1014]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1015]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1016]={guid'
(snip)

Here is a copy of the actual execution plan after it has completed the first few iterations of the chunking loop. Just take the xml and save it as a .sqlplan file and it should be able to be opened in Management Studio.

What am I doing wrong that is causing this not doing a heap insert?


texts: MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster

texts:
I Get the following error when adding the second instance to a MYSQL Innodb Cluster

ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. Please change the server ID of the instance to add: all members must have a unique server ID.
Cluster.addInstance: Access denied for user 'XXX'@'XXX' (using password: YES) (RuntimeError)

However if I check the two serverid's I can confirm that they are different

Server 1

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Server 2

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Please advise?


texts: Optimized MySQL "optional" FULLTEXT search on multiple tables

texts:
I need to select data with optional columns in where clause based on client input.
my query is like this:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id
where a.status=1
AND (0=@bid OR a.b_id=@bid)
AND (0=@cid OR a.c_id=@cid)

@bid and @cid are client inputs, the values must be >0 with default to 0 if client does not provide the input.

client can provide no inputs so it will select all data where status=1 ignoring b_id and c_id columns

client can provide @bid or @cid or both and the query will select accordingly.

table engine is InnoDB, columns are indexed, foreign key and relationships set.

so far everything is fine. EXPLAIN SQL shows selection is done based on indexes provided.

now I need to add full-text search on all 3 tables to query is where I have problems. text search is also optional if client provides any keyword to search or not.

fulltext index is defined for a.title, b.txt and c.txt

I changed the query to:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id
where a.status=1
AND ('0'=@keywords OR (MATCH(a.title) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(b.txt) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(c.txt) AGAINST(@keywords IN BOOLEAN MODE)))
AND (0=@bid OR a.b_id=@bid)
AND (0=@cid OR a.c_id=@cid)

the query seems to be working returning the results I want but the explain query returns type=ALL and a full table scan so no the query is not working in an optimized way.

if I change the OR between matches to AND then explain query returns type=fulltext and select is done on FULLTEXT index, but I need the OR style.

I was thinking about joining different result sets but couldn't find out how since the input is optional and there might be no input and therefore no full-text search needed to be done.

any solutions?

EDIT:

OK with thanks to jkavalik's comment and Rick's answer it seems I need to add some clarifications:

actually I am using a data service from WSO2 Data Services Server. so I am just passing the input parameters to the data service and I can not generate the select query based on user input. (there's an option to pass part of a query string as input parameter but I am not planning to do that for security reasons)

so I have two options:

write separate queries which accept different sets of input parameters for each case of input parameter combinations. deciding to send the parameters to which query based on user inputs. well the optional inputs being 10+ in a real situation, that will be lots of queries and don't seem to be the right solution. optional fields may change over time, maintenance in future is expensive. doesn't looks like the standard way to handle this.
write a single query in a way it can handle the optional fields.

I prefer option 2 unless there is a third option I am not aware of?

about the @variables they are either being replaced by input parameter's value or the default value (0 in this example) before query being passed to and executed in MySQL. and actually they are :variable not @variable but I changed it to @ in this example for better reading or understandings.


texts: How to get the Oracle Text Index's underlying Virtual XML Document?

texts:
The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML document for each row.
Also, in certain situations, we can use user datastore to define stored procedures that synthesize documents during indexing. The procedure can grab the data and put them in an XML format. A good example is shown here.
The Question
Is there a way to retrieve or display the underlying virtual XML document for each record? How?


texts:
I have two tables

Account table

id INT, username TEXT, password TEXT, proxy_id INT, enabled BOOLEAN

Proxy table

id INT, proxy_ip TEXT, proxy_port INT

I would have a loop that goes through accounts one at a time. I want the least used proxy to be assigned to table.proxy_id.

for example if we have 2 proxies in the proxy table and 5 accounts in the Account table

1 10.0.0.1  4000
2 10.0.0.1  4001

Our accounts

1 david    password 2    enabled
2 mark     password 1    enabled
3 jessica  password 1    enabled
4 ashley   password NULL enabled
5 allan    password NULL enabled
6 james    password 2    disabled

My program will loop through all the enabled accounts, it will assign the least used proxy in the total enabled accounts to the account. In the example above David, mark and Jessica already have a proxy set. So the loop will go through Ashley and proxy with id 2 needs to be assigned to Ashley as it is the least used. For Allan proxy 1 or 2 can be assigned since it would be the least used in any case. James should be ignored since his account is not enabled.

I hope I was clear in my question. I think this would need to be done in two queries?

How would these queries look like?


texts: Securely remove columns (and traces thereof) from a SQL database

texts:
I've come upon a database that has previously held sensitive data (think "passwords/credit-card-numbers" kind of sensitive) in plain text. All of the sensitive data in question has been deleted, but I'm concerned that if accessed by someone with malicious intentions, the data might be recoverable through backups and/or transaction logs.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?


texts: Is there any reason or performance benefit in having two log files for a single database and on different drives?


texts: PostgreSQL: COUNT(*) uses a sequential scan, not index

texts: Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?


texts: Limit results to the first 2 ranking rows

texts:
In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each PARTITION.

Example:

ID   Name    Score    Subject
1    Joe      100      Math
2    Jim      99       Math
3    Tim      98       Math
4    Joe      99       History
5    Jim      100      History
6    Tim      89       History
7    Joe      80       Geography
8    Tim      100      Geography
9    Jim      99       Geography

I want the result to be:

SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC)
FROM Table

Subject        Name      Rank
Math           Joe        1
Math           Jim        2
History        Jim        1
History        Joe        2
Geography      Tim        1
Geography      Jim        2

I want only rank 1 and 2 in each category. How do I do this?


texts: How to make a function from a concrete SELECT statement in Oracle SQL?

texts:
How to replace a concrete value with a variable in Oracle SQL? I have

 select 5, min(id) from my_table where id > 5 --AND ... complex condition
 UNION ALL
 select 6, min(id) from my_table where id > 6  --AND ... complex condition
  UNION ALL
  ....

 | 5 | 6 |
 | 6 | 8 |
 ...

How to wrap it in a function executing pseudocode below?

for ( $i in ( select id from my_table)){
    UNION ALL
    select $i, min(id) from my_table where id > $i  --AND ...
 }

To make it clear, I am looking for a general method to turn a select with hard wired values into a function that accepts variables. Note the --AND part.
This is NOT a question about using lead and over but about replacing values with variables.

I am trying to replace START WITH ID=10 with a variable in this query.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = 10
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

Apparently I can use this template, but do i have to use a pointer for this?

--creates a function
CREATE OR REPLACE Function dummy_function   ( name_in IN number )
   RETURN number
IS
   cnumber number;
   cursor c1 is
   SELECT weight FROM dummy_links WHERE ID = name_in;
BEGIN
   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;
   close c1;
RETURN cnumber+1; --to have some diversity we add 1

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

--calling a function
SELECT id, weight, dummy_function(id) AS weight_plus_one FROM dummy_links;


texts:
CREATE TABLE `csptargets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `cspcode` varchar(11) NOT NULL,
   `vol`decimal(20,3) NOT NULL DEFAULT '0.000',
   `day01` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day02` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day03` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day04` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day05` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day06` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day07` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day08` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day09` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day10` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day11` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day12` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day13` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day14` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day15` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day16` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day17` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day18` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day19` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day20` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day21` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day22` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day23` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day24` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day25` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day26` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day27` decimal(20,3)  NULL ,
   `day28` decimal(20,3)  NULL ,
   `day29` decimal(20,3)  NULL ,
   `day30` decimal(20,3)  NULL ,
   `day31` decimal(20,3)  NULL ,
   `week1` varchar(11) NOT NULL,
   `week2` varchar(11) NOT NULL,
   `week3` varchar(11) NOT NULL,
   `week4` varchar(11) NOT NULL,
   `week5` varchar(11) NOT NULL,
   `week6` varchar(11) NOT NULL,
  `cnt` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqueTarget` (`cspcode`,`start_date`,`end_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1

In this table I want to access a particular column.

I can get the current date using

SELECT @day:=DATE_FORMAT(curdate(), '%d');
set @daily:=concat ('day',@day);

Now for today's date I have day28 .

But on using select @daily from csptargets it's giving value as 'day28', not the value I have in column.

Is there any other way to use the specific column name?


texts: Kerne module for Oracle 11gR2 RAC on Linux

texts:
As per Oracle, it requires three Linux packages to run ASMLib:

oracleasm - kernel module
oracleasmlib-2.0 - ASMLib libraries
oracleasm-support-2.0 - Admin for ASMLib

This library versions are OS dependent, but my RAC is working fine without the oracleasm  kernel module.

 $ rpm -qa | grep 'oracleasm'
oracleasm-support-2.1.8-1.el6.i686
oracleasmlib-2.0.4-1.el6.i686

And

$ oracleasm listdisks
DB_DISK1
DB_DISK2
FRA_DISK1
FRA_DISK2
OCR_VOTE1
OCR_VOTE2
OCR_VOTE3

Someone can please guide why its running perfectly without having main kernel module?


texts:
I am creating a web interface for upcoming events in a community. The users can create an event, join an event, and also leave an event. The interface will list the upcoming events with the following fields: Event name, event datatime, event location, event participants.

My question is how should I store the users for each event? I am using Mysql. Since users can create a new event, and they can also join or leave an event by others, is it better for me to create 2 link tables? One to store the creation, the other to store the Join/leave?

I hope this question is specific enough, and any suggestions are gratefully received.


texts: What are my locking options for a Merge statement?

texts:
I have a stored procedure that performs a MERGE statement.

It seems like it locks the whole table by default when performing the merge.

I'm calling this stored procedure inside of a transaction where I'm also doing some other stuff and I wish it would only lock the rows affected.

I tried the hint MERGE INTO myTable WITH (READPAST) and it seemed to lock less. But there was a warning in the ms doc that said it could insert duplicate keys, bypassing even the primary key.

Here is my table schema:

CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'WANG')
INSERT INTO StudentDetails
VALUES(2,'JOHNSON')
GO

CREATE TABLE StudentTotalMarks
(
Id INT IDENTITY PRIMARY KEY,
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
GO

Here is my stored procedure:

CREATE PROCEDURE MergeTest
    @StudentId int,
    @Mark int
AS

WITH Params
AS
(
    SELECT @StudentId as StudentId,
        @Mark as Mark
)
    MERGE StudentTotalMarks AS stm
    USING Params p
    ON stm.StudentID = p.StudentId
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = p.Mark
    WHEN NOT MATCHED THEN
        INSERT(StudentID,StudentMarks)
        VALUES(p.StudentId, p.Mark);
GO

Here is how I'm observing the locking:

begin tran
EXEC MergeTest 1, 1

And then in another session:

EXEC MergeTest 2, 2

The second session waits for the first to complete before proceeding.


texts: why would rebuild index reduce btree leaf numbers

texts:
Rebuilding an index might reduce the number of leaf nodes by about 20%
  - 30%. The most you can possibly expect from this reduction is 20%-30% for very expensive operations like a FULL INDEX SCAN. The typical
  INDEX UNIQUE SCAN gain of an index rebuild is 0%-2% because the depth
  of the index is not reduced by the rebuild.

In my understanding , the btree structure/shape is almost uniqe. why would rebuild the btree reduce leaf numbers ?

is there any uncertainty around the btree build process ?

Myth: Indexes Can Degenerate


texts: Can't connect to MySQL Cluster Storage (data) nodes

texts:
I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another machine using NDBAPI.

When I tried to connect from the same host it worked, but when I try to connect from a different machine I get a socket timeout.

Any clues? There is no strange network setup.

Thanks!


texts: How to combine 2 SQL statements into one?

texts:
I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a way ONE will work for all? Each years will have 'CTX_' in it. thanks

USE CTX_2015

DBCC SHRINKFILE(ctx_2015_log)
BACKUP LOG ctx_2015 TO DISK='NUL:'
DBCC SHRINKFILE(ctx_2015_log)

USE CTX_2016

DBCC SHRINKFILE(ctx_2016_log)
BACKUP LOG ctx_2016 TO DISK='NUL:'
DBCC SHRINKFILE(ctx_2016_log)


texts: Temp tables not being created in SQL Server Azure

texts:
I have created a stored procedure that creates multiple temp tables for use in the sproc and then drops them at the end of the sproc. The problem I am encountering is I can execute the stored procedure just fine in management studio, but when I attempt to create an SSIS data flow that retrieves the rows returned by the sproc, it give me the following error:

  Invalid object name '#tempRuns'

Is there something special I need to do in order for temp tables to work with SSIS in Azure?


texts: Calculating the Path of a Row in an Adjacency List When Recursive CTE Are Not Supported

texts:
I'm currently transitioning a SQL backend from SQLite to CockroachDB, the latter being a semi-PGSQL-compatible database.

I have multiple tables which at their core are adjacency lists, i.e.:

CREATE TABLE IF NOT EXISTS foobar (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  ...
  parent_id INTEGER
);

With SQLite, I used this query to retrieve the elements in the full path of a given row:

WITH RECURSIVE path(level, name, parentid) AS (
    SELECT 0, name, parentid
    FROM {TableName}
    WHERE id = @id
    UNION ALL
    SELECT path.level + 1,
        {TableName}.name,
        {TableName}.parentid
    FROM {TableName}
    JOIN path
        ON {TableName}.id = path.parentid
)
SELECT name
FROM path
ORDER BY level DESC;

However, CockroachDB doesn't support any kind of WITH statement.

So the question is: In this database, what is the most efficient way of porting the above statement.

The full path for each row (usually about 2 to 10 million per table) only needs to be calculated once, and the table itself is not expected to change often if ever. So I'm planning to save the results from this query permanently using CREATE TABLE AS.

I am using the CockroachDB 2.0beta.