texts: How to hot-backup mysql slave database with read operations

texts:
I have a slave database of about 120GB in size on which application is performing select statements.

I also have a backup system (Acronis backup) available to me from a hosting company. Unfortunately backup agent is not mysql-aware. It can perform file backups and whole machine backup - it makes a snapshot of machine. It also enables to run a script before/after a backup and before/after snapshot creation.

I went with simple script making flush tables with read lock before making a snapshot and removing a lock after. Unfortunately, because of long-running select statements, it happens that acquiring a lock takes a lot of time.

Script was waiting for lock for some time (300 secs) and then killing the session. I thought that I will be able to cancel acquiring a lock and allow application to run. Unfortunately it appears it is not possible (or I am unable to do it correctly).

Is there any command sequence that would allow me to create a valid snapshot and not interrupt database functions for too long? For example, would:

stop slave
flush tables
make snapshot
start slave

or similar do the trick (some data de-synchronization between master ans slave is not a big issue for my app)?

I was looking into some alternatives like xtrabackup or mysqldump to make a backup and simply copy files later but:

Xtrabackup needs at least twice as much space as database and, because of that, I cannot use it.

Mysqldump restore time is huge comparing to copying files.

Taking above under consideration I would love to stick to 'cold' backup with snapshot or a solution like xtrabackup that could provide compressed data without intermediate steps.


texts:
Struggling with a task I've been given. We have a large SQL Server 2012 database (almost 2TB) with multiple filegroups. The filegroups are basically split up into different years, so many of them contain older data. The tables also contain 'image' data. All filegroups are read/write and the database is used 24/7. My task is to restore only the PRIMARY and current year's filegroups to a different instance of SQL. We do full, diff, and log backups on a regular schedule and I'm able to restore individual filegroups to the other instance. However, I'm unable to bring the database online unless I restore all filegroups.

In the past we've been doing a full restore to the other environment then removing what we don't need. This ends up taking longer than is acceptable now and uses too much disk space. Is there anyway for me to only restore the filegroups I need and still bring the database back online? Any and all help is greatly appreciated.  


texts: how to insert a list of varchar values, into a single column table?


texts:
I've been using sp_BlitzFirst and logging the results every 15 minutes for the past 5 days. Using the new Power BI report from FirstResponderKit.org, I'm seeing a VERY large range of FILESTREAM_WORKITEM_QUEUE waits. The range for this wait is anywhere from near 0, to 2942 minutes. Total for the past 5 days is 16,406 minutes. This includes the weekend, which the server only has light usage.

What causes this wait and should I be concerned that it sometimes get above 2000 minutes for a 15 minute period?

This is SQL Server 2012 Standard with a 10-core processor.


texts: General info on Resources for Running SQL Server queries

texts:
Does anyone know of online or otherwise resources for running queries?
My copy of SQL Server did not install correctly, after many attempts on my part.

Does anyone know of any resource in which I can test my SQL queries?
I am in New York City, in case there are public places with SQL Server access.

EDIT: Someone suggested I may be able to run queries with MySQL (including running a query that allows me to create the database I want to query ).

Is this true?

Thanks.


texts:
Shutting down a database before doing an upgrade or a patch can be done several ways.

shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown;

or

alter system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;

Of course there are other options as well.  Which should be preferred and why?


texts: List of databases which a user can access in SQL 2008 R2

texts:
Is it possible to create a list of all the databases where a specific user can access?

For example,

My user is 'Domain\MarySmith'. This user can access on a single database or can access many databases.

I need a query like this:

SELECT All_Db_Name_I_Can_Acces
FROM ServerSql
WHERE Username = 'Domain\MarySmith'


texts:
I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and execute. I entered a rule for the firewall outbound port 587. I tried another mail account and profile with the same unsent issues. The only entries in the logs (db mail logs) are starting and ending of the service. There are no errors anywhere that I can find. The emails appear to simply enter the send queue and never leave it. The accounts can send and receive email on their own and from a SQL Server 2014 instance on another machine.

I've got a queue of items with sent status "unsent" and checked all the normal places with expected results in all of them, aside from a long queue of unsent mail:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC

SELECT * FROM dbo.sysmail_mailitems

SELECT * FROM dbo.sysmail_sentitems

USE msdb
SELECT sent_status, *
FROM sysmail_allitems

 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

 EXECUTE msdb.dbo.sysmail_help_status_sp

I have tried turning it off and on again... so did I miss a DMV etc. that could shed light on this situation? Is this a known issue with SQL Server 2016 that I haven't in my searches? Any other possible steps to get this mail sent?


texts:
I have a base table used everywhere called Customers.  Of course, its unlikely that table will ever grow past int size, but it is keyed on a bigint nonetheless.

I don't believe that I can change the type of this identity column to int.  But what is your advice for building stored procedures, table type variables etc that refer or carry data for this column?  Should I perpetuate bigint in these routines?  Will they run leaner if I use int, leaving it to the engine to run implicit conversions?


texts: Bulk insert from a complex text file into a table

texts:
I want to use bulk insert to load a text file into a table, but my coding is only able to load half of the text file because my text file has two parts:

(ID, CompanyName and Date) as one part; and
(Name Dept, Status, Age and Gender)

The (ID, CompanyName and Date) section appears only once in the file. I want it repeated in the rows when inserted to the table.

My coding is only able to load with one format text file.

Below is my text file example:

  A1242
  CompanyName
  Date 2019-11-28 18:58:04
  Name Department Status Age Gender
  John R&D Active 23 Male
  James R&D Active 21 Male

How can I use bulk insert to load the above text file into a table looking like the below example?

  A1242 CompanyName 2019-11-28 18:58:04 John R&D Active 23 Male
  A1242 CompanyName 2019-11-28 18:58:04 James R&D Active 21 Male

Below is my coding:

-- Procedure Load Text File
ALTER procedure [dbo].[ImportFiles]
@FilePath       varchar(1000) = 'c:\Transfer\' ,
@ArchivePath        varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask       varchar(1000) = 'bcp*.txt' ,
@MergeProc      varchar(128) = 'MergeBCPData'

AS

set nocount on

declare @ImportDate datetime

select @ImportDate = getdate()

declare @FileName       varchar(1000) ,
@File           varchar(1000),
declare @cmd varchar(2000)

create table ##Import (s varchar(8000), sd varchar(8000))
create table #Dir (s varchar(8000), sd varchar(8000))

/*****************************************************************/
-- Import file
/*****************************************************************/
select  @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'

while exists (select * from #Dir)
begin
select  @FileName = min(s) from #Dir
select  @File = @FilePath + @FileName

select  @cmd =      'bulk insert'
select  @cmd = @cmd +   ' ##Import'
select  @cmd = @cmd +   ' from'
select  @cmd = @cmd +   ' ''' + replace(@File,'"','') + ''''
select  @cmd = @cmd +   ' with (FIELDTERMINATOR=''\t'''
select  @cmd = @cmd +   ',FIRSTROW = 5 '
select  @cmd = @cmd +   ',ROWTERMINATOR = ''\n'')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete  #Dir where s = @FileName

exec @MergeProc

-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath +
@FileName
exec master..xp_cmdshell @cmd
end

drop table ##Import
drop table #Dir

--Procedure Insert
/****** Object:  StoredProcedure [dbo].[MergeBCPData]    Script Date:
11/28/2019
8:32:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[MergeBCPData]

AS
set nocount on

-- insert data to production table
insert  BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld5
--fld4
)
select
fld1    = substring(s,1,12) , --testing
fld2    = substring(s,15,4) , --testing
fld3    = s ,
fld5    = s
--fld4  = convert(datetime,substring(s,26,19))
from    ##Import

--Exetuce the stored procedur
exec ImportFiles 'D:\Piccolo\' , 'D:\Piccolo\Archive\' , 'bcp*.txt',
'MergeBCPData'

I cannot change the file format and external application is my backup plan.


texts: MySQL - How can I speed up a 50+GB fixed width text file import for a 1000+ column table? (Can't remove columns)

texts:
I'm using MySQL 5.7.17 to import a ~50GB fixed width text file with over 1000 columns, but after 24 hours it still hasn't finished running.  I'm using MyISAM to get around the InnoDB column limit, and also because I thought it would be faster than InnoDB for importing (source here: https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/).

Everything I'm importing is from a fixed width text file, and I'm adding all of the text file data to CHAR rows in a ~1200 column table like this:

CREATE TABLE bigFile.BigFileTest (
  `var1` CHAR(3),
  `var2` CHAR(5),
  `var3` CHAR(12),
  ...
) ENGINE=MyISAM CHARSET = latin1;

LOAD DATA LOCAL INFILE
'C:\\path\\to\\file'
INTO TABLE bigFile.BigFileTest
(@row)
SET `var1` = TRIM(SUBSTR(@row,1,3)),
  `var2` = TRIM(SUBSTR(@row,4,8)),
  `var3` = TRIM(SUBSTR(@row,9,20))
  ...
;

I'd definitely prefer to not have a ~1200 column table, of course, but it's a requirement for the data I'm working with and I'm unable to change it.

Is there any way to speed up the import process?  I don't have much experience with database administration, so even basic changes you're all familiar with could be things I skipped.  I'm using MySQL Workbench with the defaults for everything except I specified ENGINE=MyISAM (as shown above).

Thanks for your help!


texts: Product Attribute System While Avoiding EAV

texts:
I'm working on building an attribute system for products. The issue that I'm running into is that various products can have very different attribute requirements.

Some E-commerce websites such as Magento use an EAV system. That's something that I'd like to avoid due to performance issues, database cleanliness/complexity/control.

So far I'm leaning towards using a variety of tables. For example I might have a table of attributes that pertain to medical devices and then a different table that is for toys and games. Any attributes that are generic would just fall into the actual product table. If I do choose to go with this option, then I suppose my product table would have a column which represents which attribute table is used.

I'm not actually a Database Administrator, so I don't really know what's best. I hope that someone is able to give me some insights into a good implementation or acknowledge that my current thought process is in the right direction.

Thank you


texts: MySQL 8 can not enable Mast-Master Replication after rollback from GTID Replication

texts:
can you help me with this issue please?
I have rolled back from GTID replication to traditional (master-master replication with binlog), and at the same time I’m adding new node to the cluster as replica, I have about 3TB of data dump, I have took the dump while the gtid was on but I turned it of later after the dump/restore completed I done all the steps to turn on master-master replication but i’m getting the follower error:
IO Thread STOPPED (err: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate GTID-transaction when @@GLOBAL.GTID_MODE = OFF, at file /mnt/data/mysql_data/mysql_bin_logs/mysql-bin.000995, position 903927020.; the first event 'mysql-bin.000995' at 903927020, the last event read from '/mnt/data/mysql_data/mysql_bin_logs/mysql-bin.000995' at 903927099, the last byte read from '/mnt/data/mysql_data/mysql_bin_logs/mysql-bin.000995' at 903927099.')
SQL Thread RUNNING.

I'm using MySQL 8 community installed on RedHat 7.9 for both
my.cnf on Master 2 Server:

interactive_timeout=60
default-authentication-plugin=mysql_native_password
bind-address = 0.0.0.0
datadir=/mnt/data/mysql_data
socket=/var/lib/mysql/mysql.sock
log-error=/mnt/data/mysql_logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 100000
binlog_expire_logs_seconds = 1209600
log-bin=/mnt/data/mysql_data/mysql_bin_logs/mysql-bin
server-id = 2
binlog_do_db = edxapp
binlog_do_db = edxapp_csmh

relay-log="/mnt/data/mysql_data/mysql_relay_log/relay-log"
auto-increment-offset = 2
slow_query_log = 1
slow-query_log_file = /mnt/data/mysql_logs/slow_mysql.log
long_query_time = 20

innodb-dedicated-server=on
read_only=OFF
super_read_only=OFF
tmpdir=/mnt/data/tmp

my.cnf on Master 1 Server:
default-authentication-plugin=mysql_native_password
bind-address = 0.0.0.0
datadir=/mnt/data/mysql_data
socket=/var/lib/mysql/mysql.sock
log-error=/mnt/data/mysql_logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 100000
binlog_expire_logs_seconds = 1209600
log-bin=/mnt/data/mysql_data/mysql_bin_logs/mysql-bin
server-id = 1
binlog_do_db = edxapp
binlog_do_db = edxapp_csmh
replicate-do-db = edxapp
replicate-do-db = edxapp_csmh
relay-log="/mnt/data/mysql_data/mysql_relay_log/relay-log"
auto-increment-offset = 1
slow_query_log = 1
slow-query_log_file = /mnt/data/mysql_logs/slow_mysql.log
long_query_time = 20
innodb_buffer_pool_size = 1300M
read_only=OFF
super_read_only=OFF
!include /etc/my.cnf.d/secrets-backup.cnf
tmpdir=/mnt/data/tmp


texts: MySQL: Given a list of row values, how do you only return the changed rows?

texts:
This is my first S/O question! I'm stumped on what seems to be a basic query; given a list of values for various rows, return only the values that have changed. I am building a live-updating feature for a ordering system and I need to update rows on the client browser that have changed since the last (5 second) polling interval. Since I'm dealing with thousands of rows I need to only update what's changed.

Here's what my table looks like:
id, order_number, department

My client sends a list of two values (x1000+ items):
order_number, department

'department' is the value that I'm looking to see if a change has occurred.

I need a way to query the DB once with the entire list from the client and have MySQL return a list of only the changed items. Given a list of 1500 orders, it's likely that only one will ever be changed at a time, so I think for efficiency it makes sense to have the DB engine do this for me rather than looping through the list and doing a query each time. I tried doing it with a temp table and while it kind of worked, I found that the DROP TABLE at the end precluded my query results from being returned:

CREATE TEMPORARY TABLE `orderlist` (
    `order_number` varchar(128) DEFAULT NULL,
    `department` int(11) DEFAULT '1'
);
INSERT INTO orderlist (order_id, department) VALUES ('180425','2');
INSERT INTO orderlist (order_id, department) VALUES ('180426','2');
INSERT INTO orderlist (order_id, department) VALUES ('180427','3');
SELECT orders.order_number, orders.department
FROM orders
JOIN orderlist USING(order_number)
WHERE orderlist.department != orders.department;
DROP TABLE orderlist;


texts: how to get a schematic diagram/layout of the an existing database

texts:
I am looking for a schematic diagram of an existing database.
More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing database in MYSQL,I am using MYSQL workbench as an IDE,does it have database modelling tools to extract this?if not what other tools can do this?

UPDATE:


texts: 2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

texts:
Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql

I got answer (thanks coytech ) but i need one more column in its :

as per :

id | mid | pid | owgh | nwgh |
1    3      12    1.5    0.6
2    3      12    1.5    0.3
3    3      14    0.6    0.4
4    3      15    1.2    1.1
5    4      16    1.5    1.0
6    4      17    2.4    1.2
7    3      19    3.0    1.4

i got answer

  Select mid ,
        COUNT(distinct pid) as cpid  ,
        SUM(nwgh) as totalnwgh  from test  GROUP BY mid

sqlfiddle : http://sqlfiddle.com/#!9/45e68/2

mid  cpid       totalnwgh
3      4          3.8
4      2          2.2

But above i need one more column that's as below : totowgh

mid cpid        totalnwgh  totowgh
3      4          3.8        6.3 (DISTINCT value as per pid column)
4      2          2.2        3.9

where totowgh = 6.3 come by DISTINCT value of owgh column  as per distinct pid column

mid = 3 has count 5 but distinct pid  = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.

As pid=12 , column owgh value 1.5 is twice ...hence its is count 1 time hence,

1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )

Please note : i need distinct owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2

SEE WHAT I MEANS   : sqlfiddle.com/#!9/2a53c/6


texts: Easy way to display HTML results from Oracle (SQLDeveloper) in Browser?

texts:
I'm curious, if there is an easy way to display any kind of html reports generated in SQL-Developer by packages like e.g. dbms_sqltune.report_sql_monitor() in an external browser like Firefox etc.
Currently, I'm creating html reports via SQLDeveloper and doing copy&paste into a new file with .html suffix and open it from any browser. It works, but it is quite uncomfortable and I wasn't able to find any solution anywhere.
For me it would be sufficient to copy&paste into any Browser and get the formated result of it.
Any ideas?


texts: Saving the "view data options" in the "edit data" window in pgAdmin III

texts:
I wonder whether there is any way to save the "view data options" in the "edit data" window in pgAdmin III:

It's annoying to have to input them again each time one opens a new "edit data" window.


texts: MySQL DB Causing Spike-up on Windows Server CPU

texts:
This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it doesn't matter if I kill them, they keep coming back. What can I do to prevent that? Help!
Here's the log:

---TRANSACTION 284071526856416, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526852056, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526902632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526853800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526845080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 39922654, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 1654, OS thread handle 6724, query id 46131 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'imtbh6fl9rldnoi44vjnmepf70', `first_visit_at` = '2020-01-14 19:49:19', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763150')
---TRANSACTION 39922653, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1669, OS thread handle 5184, query id 46128 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'fvobv8l301a79ece0006vot6a5', `first_visit_at` = '2020-01-14 19:49:23', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763153')
---TRANSACTION 39922646, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1623, OS thread handle 5048, query id 46079 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"919e25a2e43969e5d6bfbede7bb4e55d\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:14:\"159.138.159.72\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s:
---TRANSACTION 39922645, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1624, OS thread handle 6820, query id 46078 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"d1e4c0600c606a203e398f2844d33ada\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:15:\"159.138.153.198\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s


texts: MySQL optimization - more tables vs more columns/rows

texts:
Im new to SQL and looking to build a database. As an example to what I'm trying to do is have a database with about 5000 stock tickers. Each ticker will have about 35 data-points, corresponding to specific dates. All data will be INT or a small string.
I'm wondering if it's better to have each ticker represent a table, with all its data-points inside the table (about 15 columns and 20 rows), or should I put this all in one table, with the index being the ticker symbol, then having a bunch of columns.
It seems easier to make a new table for each ticker, as I can put the column titles as dates, and row titles as what the data represents, but not sure if this is optimized or would cause problems with multiple people searching for different tables.
Thanks!
EDIT: Here is an example of the table I'm looking to create, is this possible to have 2 rows indexes?


texts: Referring alias name in subselect returns "Unknown column in 'where clause'"

texts:
I have following table:

+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| Field      | Type      | Null      | Key      | Default      | Extra      |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| id         | int(11)   | NO        | PRI      |              | auto_increment |
| date       | datetime  | NO        |          |              |            |
| a          | int(11)   | NO        |          |              |            |
| b          | int(11)   | NO        |          |              |            |
| c          | int(11)   | NO        |          |              |            |
| d          | int(11)   | NO        |          |              |            |
| e          | int(11)   | NO        |          |              |            |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
7 rows

I need to get number of rows, number of rows where a is '1', number of rows where a is '0' and frequency of '1's as percentage, in each hour of day. So, I ran this query:

select day,hour,c,100.0*(a1/c) as percentage,a1 from
(
    select day(date) as day,hour(date) as hour,count(*) as c, (select count(*) from t where a=1 and hour(date)=hour) as a1 from t group by hour(date)
) as T;

Which I have Debian 8.0 - MySQL server version (5.5.?), and it returned correct results. Though it fails at home on Windows 7 MySQL 5.7.9 with following error:

Error Code: 1054. Unknown column 'hour' in 'where clause'

My question is if SQL is evaluated from inside out, so how the first query even worked? And what is the standard query to get this results?

Note
Just installed 5.5.13 and above query is working.


texts:
I have a data table with about the following schema:
ID (Int)             --ID of the entry
Parent_ID (Int)      --ID of the parenting entry, or 0 if there is none
Level (Int)          --Level in the hierarchy of this entry. Starts at 1.
User (int)           --ID of the user who owns this entry

I've seen this type of hierarchy referred to as BOM (bill of material).
I must admit, I'm not very experienced with how to approach BOM from a SQL perspective. It doesn't seem that difficult to approach it from procedural code in an application but SQL is another beast here.
Anyway, what I would like to achieve is to get all entries of a certain user with the ID X. But since it's possible that entries earlier in the hierarchy belong to another user, just filtering with WHERE user = X may and in all likelihood will break the hierarchy.
In other words, I need all entries WHERE user = X but also all entries higher up in the hierarchy.
I'm currently reading up on CTEs as I have an inkling this might be the right approach but I'd appreciate any hints in the right direction. If you have a working code example you're willing to share, that would be much appreciated, too.


texts:
We have an AX server that we would like a group of users to be able to retrieve data from but limit their access to just the data needed. Normally this could be done with a stored procedure and give the the group permission to only execute the stored procedure and use the "execute as" to have the procedure run under an account that does have access to the underlying tables. However AX controls the lifetime of stored procedure so we can't just create an arbitrary stored procedure and call it good.

To remedy the situation, we would instead have the stored procedure on another SQL Server and use a Linked Server so that the users could get the data they need but not have direct access to the tables of the AX server.

The problem is in setting this up we keep getting:

  Msg 7437, Level 16, State 1, Line 3
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.

So the question is: How do we get this scenario to work with Linked Servers or is there a better way to have users get the data they need without direct access to the AX server?


texts: Deploying database changes in high availability environment

texts:
We are currently looking at our deploy process, which consists of an application tier and data tier for a web solution.

We'd like to develop a deployment strategy that does not require downtime for our sites.

From an application point of view this is simple enough - we have a load balanced environment, and can turn servers on and off as we deploy the code. However we are not really sure how to approach the database side of things, which at the moment is a bit of a single point of failure.

The database is used for both reads and writes, and it is not really feasible (as far as we can see) to turn either off without bringing sites offline. So we need to keep the database in sync with whichever version of the application is currently servicing requests. We also need to allow enough time for the database deploys to be made, without causing any problems.

Is this a solved problem in DBA circles? Does anyone have any tips, stories or resources that might help us approach this problem? At the moment we are not really sure what our first step in this process should be.

We are using SQL Server 2008


texts:
I have oracle 11g that runs on Linux serve.
I have a Flash Recovery Area, I backup my archive logs to another location.

presently I have very little space left in my Flash Recovery Area (FRA) thereby resulting in my archivelog getting filled up quickly and making my DR unable to receive Redo logs.

I wish to free some space in the FRA.
please which of the directories in the FRA Can I touch in order to free some space.

Here is the information of the directories in my FRA
autobackup   3150
archivelog   6962
controlfile  81
datafile     567774
onlinelog    13338

I know  I can't touch the control file.
am new here and afraid to mess things up.

if I can't delete files from any of these directories to free some space, what is my possible alternative of getting some more space in my FRA.


texts: Index for speeding up sorting by boolean, timestamp

texts:
For a website that displays recent news, I have this query on the home page:

SELECT *
FROM "cms_news"
WHERE
  NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND
       "cms_news"."state" = 'published'
ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;

Here's the table definition:

CREATE TABLE cms_news (
    id integer DEFAULT nextval('cms_news_id_seq'::regclass) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    title character varying(510) NOT NULL,
    text text NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    state character varying(20) NOT NULL,
    flash boolean NOT NULL,
);

With about 67000 entries in the table, the above query to select the newest 7 published news entries takes about 100ms, which seems a bit much. Here's the output of EXPLAIN:

EXPLAIN SELECT * FROM "cms_news" WHERE (NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00' ) AND "cms_news"."state" = 'published' ) ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=13600.34..13600.36 rows=7 width=1274)
   ->  Sort  (cost=13600.34..13768.27 rows=67171 width=1274)
         Sort Key: flash, "timestamp"
         ->  Seq Scan on cms_news  (cost=0.00..12321.62 rows=67171 width=1274)
               Filter: (("timestamp" < '2013-08-08 15:32:39.778988+02'::timestamp with time zone) AND ((state)::text = 'published'::text))

I'm a complete amateur (as you might have guessed already), but it looks like sorting is the real problem. What kind of index would help me here?


texts: convert default charset utf8 tables to utf8mb4 mysql 5.7.17

texts:
I tried the following steps to upgrade CHARSET to utf8mb4 and to connect it to the existing backend server.

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
update the DB connection parameters.

"user:pass@tcp(server:3306)/mydbcharset=utf8mb4&collation=utf8mb4_unicode_ci"

This is the users table schema before migration.

CREATE TABLE IF NOT EXISTS `users` (
    `id`               BIGINT(20) NOT NULL AUTO_INCREMENT,
    `email`            VARCHAR(255) NOT NULL DEFAULT '',
    `password`         VARCHAR(255) NOT NULL DEFAULT '',
    `country_code`     CHAR(4) NOT NULL DEFAULT '',
    `industry`         VARCHAR(255) NOT NULL DEFAULT '',
    `company_size`     VARCHAR(255) NOT NULL DEFAULT '',
    `company_name`     VARCHAR(255) NOT NULL DEFAULT '',
    `last_name`        VARCHAR(255) NOT NULL DEFAULT '',
    `first_name`       VARCHAR(255) NOT NULL DEFAULT '',
    `newsletter`       TINYINT(1) NOT NULL DEFAULT 0,
    `company_website`  VARCHAR(255) NOT NULL DEFAULT '',
    `job_title`        VARCHAR(255) NOT NULL DEFAULT '',
    `profile_picture`  VARCHAR(255) NOT NULL DEFAULT '',
    `verified`         TINYINT(1) NOT NULL DEFAULT 0,
    `p_updated_time`   INT NOT NULL DEFAULT 0,
    `created`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`email`)
)ENGINE = InnoDB DEFAULT CHARSET=UTF8;

Everything works fine so far.

My worry is that some of the VARCHAR columns have character constrain as 255. In this article https://mathiasbynens.be/notes/mysql-utf8mb4 and the accepted answer here How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 says updating varchar(255) to 191 is needed. However I did not encounter issues without doing that, existing DB has around 100 user records and a dry run of the above migration worked perfectly.

Question are

Is it mandatory to check and update column lengths when moving from
utf8 to utf8mb4, if not what kind of issues could arise?
Is it also mandatory to change the CHARSET of the database? Why not change only the charset of the table?

--UPDATE--

For someone who runs into foreign key violations can use the below

SET FOREIGN_KEY_CHECKS=0;

-- Insert your other SQL Queries here...
 ALTER TABLE tble_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;


texts: ora-03113 end-of-file on communication channel on creation of database

texts:
I'm trying to create an oracle database in my local machine via Database Configuration Assistant tool. At the last step, when I lunch the creation, I'm having this issue of  :
ORA-03113 end-of-file communication channel

I've looked for some documentation, but I still can't debug the issue.

Any help would be greatly appreciated :)


texts: Documentation for selecting table name returning csv of the row

texts:
It seems that in Postgres 9.2 this syntax

SELECT <table name>
FROM <table name>

Returns rows with all columns aggregated as pseudo-CSV, like so:

(1,test,48,,48,,"2016-10-29 00:47:09.348986+00","2016-10-29 00:47:09.348986+00",,1,9,1,1,,1,0,"",,"")
(6,subject,0,,,,"2016-10-30 00:54:38.203023+00","2016-10-30 00:54:38.203023+00",,1,10,1,3,,1,0,content,,)

I'm trying to find the relevant documentation for this behavior. So far I've looked in :

SELECT
Select Lists
Value Expressions
Aggregate Functions

But I can't seem to find any references to it.


texts: How to find values missing from a sequence?

texts:
I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing.
I want to find the missing lines via c_order_id and documentno.
I tried this but it didn't return the expected result:
select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

In this example  many rows missing: from 'DGPOS2003030' to 'DGPOS2003068'.
How do I write a query to retrieve them?


texts: Grant select on all synonyms of schema to another schema in Oracle

texts:
I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle.
Example:
Admin schema has all the tables. I want the User schema to access all the tables owned by Admin. So I want to grant select privilege to User. How can I write a query to accomplish this task?


texts:
Just a question, based on some current events. Is it possible and useful to set a trigger on MySQL databases, before executing specific DELETE statements?

While having periodically back-ups is a must, I thought it might be useful to make the DELETE statement more safe, by creating a back-up before actually executing the DELETE statement, for example (like a pre-commit in github, might check for syntax issues before actually committing the code).

Is it possible and recommended to have such triggers? Like I have worked with Database migrations earlier, and the problem with rollbacks was actually you executed a DELETE statement on a column and removed the new data which was added as well. I thought it would be nice to have a (partially) back-up of the data which will be deleted.

Any thoughts?


texts: Does the MySQL primary key affect the efficiency of a SELECT query?

texts:
Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of perhaps a binary search; but does MySQL actually take advantage of this option?

If so, is the primary key required to be in order? And while we're at this question, if a table is in order of a particular column--whether primary or not--would that aid in the SELECT process if that particular column was specified in the WHERE clause?

If not, what are some of the precautions I could take in building a speed-efficient infrastructure for my database?
For example, I have a table of articles where the first two columns are: an 'ID' as the primary key, and an alphanumeric ID for the URL. To show related articles, I have a table with two columns for relating article IDs. I have noticed that multi-table SELECT query is much more faster when the "related" table logs the primary key ('ID') rather than the alphanumeric ID (which is not primary nor numeric).


texts: How to make an existing column autoincrement in Oracle

texts:
I have created a table in oracle database which I named USERS and I have created a column USER_ID with type NUMBER which i set as primary key.

How can I modify  the column USER_ID to make it auto increment by 1 ?

Any help please ?


texts: Metadata overhead warnings in couchbase web

texts: I have 3 node couchbase cluster which is working fine but now days I am getting many alerts as "Metadata overhead warning. Over 54% RAM allocated to bucket" on couchabse web UI. Please help to supress these alert


texts: How to insert variable number of rows inside an SQL Function with multiple queries?

texts:
My original idea was to use a transaction, since we have three statements and I want all or none. The transaction block looks something like below:
    BEGIN;
     INSERT INTO analysis_record (user_id, status, include_tesla) VALUES
          ('{auth0_userid}', 'inserted', '{input$tesla_toggle}');
     {new_evse_query};
     INSERT INTO user_details (user_id, user_name, email_id) VALUES
          ('{auth0_userid}', '{user_name}', '{user_email}')
         ON CONFLICT (user_id) DO UPDATE SET last_submit_date = NOW();
   COMMIT;

where the braced blocks are R variables that are supplemented into the string before being sent to the DB server. When I execute the above with query parameters like $1, $2, (using DBI::dbSendQuery() and DBI::dbBind()) for the values, I get the error - Failed to prepare query: ERROR:  cannot insert multiple commands into a prepared statement.
To circumvent this, I am planning to use an SQL function. However, it is not clear how I can insert a variable number of rows into a table in an SQL function. For example, {new_evse_query} is currently generated in the application as the number of rows to be inserted is not known apriori. As a simple example, I want to do the following:
CREATE OR REPLACE FUNCTION (args)
   INSERT INTO table1 (col1, col2) VALUES
       ($1, $2),
       ($3, $4),
       ..../% The exact n here dynamic and should be supplied as function argument.%/

How can I achieve this in PostgreSQL? The current ideas I have are:

To create the function dynamically on the application side, instead of pre-defined on the database server.
Pass the values to be inserted as an array and have the logic in the function to parse the array appropriately.
Either use VARIADIC or Polymorphic functions, both of which seem scary and less documented.

My requirements are: prevent SQL injection, so the need for parameterized query in the first place; and the first insert statement generated ID is being used in the second insert statement using a strategy described here.


texts: Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?

texts:
I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition.

Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I am wondering what features will be lost by going from Enterprise down to Standard, if any.

I've tried searching around for this but there are only comparisons between editions on the same version (i.e. 2008 Standard vs Enterprise) not comparasions between different editions across different versions (i.e. 2005 Enterprise vs 2008 Standard).

Anyone got any advice or has any resource they can point me at?

thanks in advance.


texts: Share data from one database to two different db

texts:
For example I have:

database_root (with "table1_test"..)

and then I have (but those tables doesn't have "table1_test")

db_1
db_2

I want to share the data of database_root to db_1 and 2, and if someone is looking for data on table1_test on db_1 or 2... automatically the data is collected from database_root.

I don't really know if it's complicated, because I don't know so much of MySQL so... tell me if it's possible (if it's possible please tell me how to) or no.


texts: Changing Collation with a High Availability server

texts:
How do I change a DB Collation without removing the DB from high availability group? Can I just take it offline and change it? or I really do need to take it out of high availability group 1st then set to single user then alter then reset it back to multiuser and back to high availability? will it cause an issue with the partner database?

Sorry I forgot the specs. I accidentally choose a case sensitive collation type. and I want to change it to Case insensitive type. but this DB is on the high availability always on group.

Thank you


texts: Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform)

texts:
I have PostgreSQL 9.4 installed on a dual-boot machine (both on Win 7 and Ubuntu 14.10). The two systems share a drive (NTFS-formated) which includes a PSQL database directory which was created under Windows. Obviously the PSQL installations are system-specific, and they also happen to be on different partitions. The drive is automatically mounted under Linux and given full read/write/execute permissions.

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

Is this possible, and if so, how would one set this up from Linux assuming all the data was originally generated under Windows?

EDIT: I tried running:

sudo pg_dropcluster --stop 9.4 main

followed by

sudo pg_createcluster -d /db_from_windows_path 9.4 main

which appeared to work, but then when starting up the server again, I get:

sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 9.4 database server
 * Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl start -D /db_from_windows_path -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
 [fail]

It seems extremely cryptic, especially since /var/log/postgresql/postgresql-9.4-main.log is completely empty, and so is the rest of the /var/log/postgresql/ directory

EDIT 2: After some looking about, I noticed that the chown hadn't taken because NTFS systems can apparently only be set at mount time. After fixing the /etc/fstab to give ownership to postgres, I get a different error: The PostgreSQL server failed to start. Please check the log output. Which, again, resulted in no log, so I ran:

/usr/lib/postgresql/9.4//bin/postgres -d 5 -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

which gave this:

LOG:  invalid value for parameter "dynamic_shared_memory_type": "windows"
HINT:  Available values: posix, sysv, mmap, none.
2015-04-17 12:38:43 BST [3653-3] LOG:  invalid value for parameter "lc_messages": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-4] LOG:  invalid value for parameter "lc_monetary": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-5] LOG:  invalid value for parameter "lc_numeric": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-6] LOG:  invalid value for parameter "lc_time": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-7] FATAL:  configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors
2015-04-17 12:38:43 BST [3653-8] DEBUG:  shmem_exit(1): 0 before_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-9] DEBUG:  shmem_exit(1): 0 on_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-10] DEBUG:  proc_exit(1): 0 callbacks to make
2015-04-17 12:38:43 BST [3653-11] DEBUG:  exit(1)

This seems like a different problem altogether, perhaps POSIX-related?


texts: If I update a table using one connection and issue a query in another connection immediately after that, is there a chance that I can't see the changes even if it's extremely unlikely?


texts:
I'm in the process of choosing a database for a project related to biology and medicine. The main added value of a graph database will be the ability to explore and weight the relationship between different concepts according different insights such as available studies and open data.

ArangoDB provide an interesting but partisan literature about what they can do that Neo4j can't, as well as a benchmark.

https://www.arangodb.com/why-arangodb/arangodb-vs-neo4j/

https://www.arangodb.com/2018/02/nosql-performance-benchmark-2018-mongodb-postgresql-orientdb-neo4j-arangodb/

Ok... Now I'm trying to get what Neo4j can do but ArangoDB can't. Afterwards, they are the market leader for graph databases.

In the documentation Neo4j provide a few information about graph algorithms. Is it something that is only available for Neo4j? Also, I have seen that Neo4j provide a mechanisms to weight edges.

What could be actual example of strong diffenciators that could make me choose Neo4j instead of ArangoDB?


texts: MS Access: Subform only displays first entry. Continuous Form follow-up question

texts:
I am new to MS Access and Database creation in general. Currently I am working on a simple employee database that can view an employee's information and can show their current (and previous) projects and training courses within the company. Below is a snapshot of my Relationship Chart.

My question is twofold,

1.) I am working on a subform that shows the projects currently and previously taken by an employee (The Project Status table shows either Current or Previous status. More can be added later on). When I create the subform however, it only shows the first entry, regardless if the employee has multiple entries. Below is a picture of the form.

2.) Is there a more ideal way to create a form that gets sources from 2 different queries than getting a mainform and putting a subform inside? As you can see I am getting the entries on the form depending on the combo box selection. Furthermore, is there any way to make a dynamic subform that can expand depending on the number of entries it outputs? My gripe with using a Continuous form is that it also shows the blank "next" entry.

Many thanks.


texts:
My disk crashed so I reinstalled SQL Server to a new drive and restored my databases from my backup drive.  However, when I attempt to install Diagram support, I get this message:

  Cannot execute as the database principal because the principal "dbo" does not exist....

This issue is admirably answered here but the reply doesn't seem to apply in my case since running the first query in the "Solution" section returns no records so I have nothing to fix, yet I get that error message.

Here are my results from the queries suggested in that reply:

Please inform me how to resolve this matter.


texts: What is the difference between MySQL VARCHAR and TEXT data types?

texts:
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?

I was reading the list of differences and the only two of note are:

  For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.

and

  BLOB and TEXT columns cannot have DEFAULT values.

So, because of these two limitations on the TEXT datatype, why would you use it over varchar(65535) ? Are there performance ramifications of one over the other?


texts: MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?

texts:
I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).

My solution (do not work) was to add a AFTER TRIGGER listening for UPDATES and INSERTS to MYTable (and only add that TRIGGER in ReplicaDB) and copy everything on MyTableLog. Them pooling MyTableLog (and remove already processed records).

Problem: It looks like the RDS replica it is not firing the AFTER INSERT event (is only firing AFTER UPDATES). However I tested the solution in 5.7 and it worked.

Any ideas? It is a bug in MySQL? Any solution?

UPDATES:

1- I'm adding a new trigger on SlaveDB (it is not a trigger replicated from MasterDB)

2- It is working in 5.7 -> 5.7 ... the issue is in 5.6 -> 5.6 MySQL DB

3- Im adding TWO triggers (both works in 5.7 but only 1 works in 5.6)

4- Update Trigger (works in 5.6 and 5.7)

CREATE TRIGGER after_mytable_update
  AFTER UPDATE ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 0, is_processed = 0;
END

5- Insert Trigger (DO NOT WORK IN MYSQL 5.6)

CREATE TRIGGER after_mytable_insert
  AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 1, is_processed = 0;
END

As simple as it is. The INSERT trigger it **IS NOT being executed in MySQL 5.6**

(From Comment)

CREATE TABLE mytable_log_replica (
    replica_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    is_new int(11) DEFAULT '0',
    is_processed int(11) DEFAULT '0',
    id int(11) unsigned NOT NULL,
    stamp datetime DEFAULT NULL,
    user_id int(11) DEFAULT NULL,
    name varchar(64) DEFAULT NULL,
    address varchar(64) DEFAULT NULL,
    transaction_status varchar(64) DEFAULT NULL,
    ip varchar(64) DEFAULT NULL,
    cb_code varchar(16) DEFAULT NULL PRIMARY KEY (replica_id)
) ENGINE=InnoDB AUTO_INCREMENT=240878 DEFAULT CHARSET=latin1


texts:
I have a Windows 2008 R2 server that needs to be periodically reset to a clean state. Normally I would look at creating an image of the system drive, however due to organizational reasons I cannot use an image for this purpose.

So what I'm working on is using a combination of Windows System Restore and SQL's internal backup. The piece I'm missing right now is how to actually back up the SQL 2008 R2 Instance and associated configuration settings.

Basically I would like to be able to save a snapshot of the instance itself (logins, schemas, defaults, etc.) as well as anything necessary to restore the application to a working state from a bare metal restore (so things like service owners and registry keys).

Does SQL itself have a way to do this or do I have to look for another way to back up the application? For the logins, schemas and defaults, are those contained within one of the system databases?


texts: mysql 5.6 replication log-slave-updates doesn't work

texts:
We have a setup of master -> replication -> replication
the dbs are: mysql 5.7 -> 5.6 -> 5.7

on the replication 5.6 we have the following my.cnf:

[mysqld]
default-storage-engine=InnoDB
innodb_file_per_table=1
performance-schema=0

max_connections=4096
group_concat_max_len=8196
innodb_flush_log_at_trx_commit=0
query_cache_limit=128M
query_cache_size=128M
max_allowed_packet=268435456
open_files_limit=29000
#log_timestamps = SYSTEM

slow-query-log=1
#log = /var/log/mysql_query.log

server-id               = 2

log-bin

replicate-do-db = db_main
log-slave-updates

slave-skip-errors = all

however the bin-log on the server is not being updated. The relay-log IS being update. server-id on the master 5.7 is 1 and on the last 5.7 slave is 3.
all servers have log-slave-updates and upon checking with mysqladmin var|grep update we see log_slave_updates ON.

What are we missing?

Thanks!


texts:
I have Windows Server 2008 R2 with Oracle Client 11.1.0.7.0 client installed. TNSPing to 100% available databases (accessible from other machines, i.e. I can do TNS ping to these databases from other machines) returns me following error: TNS-12535: TNS:operation timeout. At the same time I can telnet to database host/port and see that proper tnsnames file is being used. Environment variables are configured as appropriate (ORACLE_HOME, TNS_ADMIN, Path). Also I can connect via telnet using service/host name + port - so this is unlikely a firewall issue.
After enabling Oracle client logging I can see following error related details in tnsping.trc:

  snlinGetAddrInfo: enrty snlinGetAddrInfo: getaddrinfo() failed with
  error 11001 snlinGetAddrInfo: exit

The only strange/suspicious thing about this machine is that it had multiple versions of oracle client installed (10, 11.1, 11.2) - removal of all versions apart from 11.1 & reinstall/repair of 11.1 haven't resolved this issue. Also it seems to me that v10 of client was not removed properly - it disappeared from Programs and Features, but files in installation folder had not been deleted.
Any advice on what to check / how to resolve this issue?


texts:
When I'm trying to start SQL Server Agent, I got this error:

  TITLE: Microsoft SQL Server Management Studio
  Unable to start service SQLAGENT$SQL_NAGA on server NAGA. (mscorlib)
  ADDITIONAL INFORMATION:
  The SQLAGENT$SQL_NAGA service on NAGA started and then stopped. (ObjectExplorer)

Can you guys help me to solve this problem? I'm using 2014 Enterprise Edition.

Using @@version I got this:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun  9 2015 12:06:16
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

I also found this error event related to SQL Server Agent in the Windows Event Log

Log Name:      Application
Source:        SQLAgent$SQL_NAGA
Date:          1/31/2016 6:17:24 PM
Event ID:      103
Task Category: Service Control
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      Naga
Description:
SQLServerAgent could not be started
(reason: Unable to connect to server 'NAGA\SQL_NAGA'; SQLServerAgent cannot start).

UPDATE:

Error log of SQL Server Agent

SQL Server and SQL Server Agent both logged on as Local System Account


texts: How to restrict multi-tenant MySQL database?

texts:
I have following table:

Create table `webservice`(
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `hostname` text NOT NULL,
  `portalid` int NOT NULL,
  `serviceid` int NOT NULL,
  `customerid` int,
  primary key (`id`)
)

I have multiple customers accessing multiple portals and services using different FQDN but same DOCROOT and MySQL login. It is multi-tenant application.

Now I want every visitor, to be able view only the data to which he is authorized to. All these users are using single MySQL connection login.

On first connect, browser is requesting the FQDN, so it should grant permission to view the associated "portalid" only rows. So this way without knowing FQDN, it is impossible to get access to rows with different portalid.

When user logs in, he's views should be restricted to only records with his own customerid.

I dont know how to do it, but I think I could use session data in SQL, which would allow to fetch the required data. However I dont know how to setup this at the moment.

I have session like this: https://security.stackexchange.com/questions/17318/how-to-secure-mysql-based-web-session-data-table


texts:
MySQL.

There is a table users with autoincrement primary key id.

There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not autoincrement.

employees.userid is a foreign key for users.id.

Thus every employee's primary key value is also users primary key value.

Now consider a third table rows of which should refer to an employee.

The question: Should the third table refer to users.id or to employees.userid?


texts:
I have basically two views named customerV and productV.

the SQL statement in the customerV view is :

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE,
                      RECIEVABLE.PHONE#, RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE
FROM         CUSTOMER_REGISTRATION INNER JOIN
                      RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#

while the SQL statement in the productV view is :

SELECT     PRODUCT, MAKE, MODEL
FROM         PRODUCTV

I just want to create a new view called serial_products which would join all the columns of the two views into a single view calledserial_products.


texts: Cannot create maintenance plan, I get the message "The task with the name '' and the creation name is not registered for use on this computer"

texts:
I just installed SQL server 2016 (Standard edition) on a new server,
I'm trying to add a Maintenance plan (I've tried back up plan, rebuild indexes and many more)
I'm getting a weird message when I'm trying to open the task:

  The task with the name "back up plan database task" and the creation name is not registered for use on this computer.

At first, I thought it was a permission issue so I went to services.msc and change the login as to a user with administrator permissions - still not working.
Restarting the server - still not working.


texts:
I would like to redact data with regex in oracle database. I know the procedure but I get unpredictable results.

I use the following script to apply redaction to a certain column with card numbers.

BEGIN
DBMS_REDACT.ADD_POLICY (
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table',
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => '(............)(.+)(........)',--DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => '\1****\3', --dbms_redact.re_redact_ccn, --DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

Expected result would be first 6 and last 4 unmasked like 123456****1234, but I get this instead:

From the documentation I would suppose the below script would work by default, but it performs a full redaction instead of just masking some chars. I get a whitespace per row.

BEGIN
DBMS_REDACT.ADD_POLICY (
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table',
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

Database used is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

This are the constants in DBMS_REDACT package:

RE_PATTERN_CC_L6_T4        CONSTANT   VARCHAR2(33) := '(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)';
RE_REDACT_CC_MIDDLE_DIGITS CONSTANT   VARCHAR2(10) := '\1XXXXXX\3';

If i put the \d in regex patter i always get full redaction. Using dot i managed to get the scrambled data from the image.

If anyone could try here is also the drop statement:

exec dbms_redact.drop_policy('my_schema','my_table','redact_my_table');

Any ideas what am I doing wrong?


texts:
I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")". Postgres REST nicely suggest to approach the problem through automatic casts:
create or replace function tsrange_to_json(tsrange) returns json as $$
  select json_build_object(
    'lower', lower($1)
  , 'upper', upper($1)
  , 'lower_inc', lower_inc($1)
  , 'upper_inc', upper_inc($1)
  );
$$ language sql;
create cast (tsrange as json) with function tsrange_to_json(tsrange) as assignment;

which works nicely if I cast tsrange::json but keeps the old formatting if I do to_json(tsrange)
select tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json
-- "{"lower" : "2010-01-01T14:30:00", "upper" : "2010-01-01T15:30:00", "lower_inc" : true, "upper_inc" : false}"
select to_json(tsrange('2010-01-01 14:30', '2010-01-01 15:30'))
-- ""[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")""

Why to_json does not use ::json casts?


texts: Adding Azure VM as 3rd node to the 2-node On-Premise Windows Server Failover Cluster - Problem

texts:
We have 2-node WSFC, which is hosted on-premise in our datacenter
This WSFC is running one clustered role which is SQL Server Availability Group

Node-1: Windows Server 2016 - two subnets - 192.168.7.xxx / 192.168.19.xxx
Node-2: Windows Server 2012 - two subnets - 192.168.7.xxx / 192.168.19.xxx

Cluster-1 CNO has two IP addresses under its resources as well - 192.168.7.xxx / 192.168.19.xxx
There are two DNS records for Cluster CNO, that resolve to 7.xxx and 19.xxx

We are trying to add Azure virtual machine (Node-3) as a 3rd Node to WSFC Cluster-1

Node-3: Windows Server 2016 - one subnet - 10.1.0.xxx

But the "Add Node" operation fails in Failover Cluster Manager
We get below errors:

Cluster service on node Node-3 did not reach the running state. The error code is 0x5b4.

Cluster node 'Node-3' failed to join the cluster because it could not communicate
over the network with any other node in the cluster.
Verify network connectivity and configuration of any network firewalls.

Cluster failed to start. The latest copy of cluster configuration data was not
available within the set of nodes attempting to start the cluster.
Changes to the cluster occurred while the set of nodes were not in membership and as a
result were not able to receive configuration data updates

We opened ports below ports in Windows Firewall on all nodes, in Azure firewall for Node-3, and in pfSense hardware firewall

3343 TCP / UDP 135 TCP 137 UDP 445 TCP

Also we setup IPSec Site-to-Site connection between Azure VNet (10.1.0.xxx) and on-premise (192.168.7.xxx), so the two networks connected and see each other

But Azure VM still can not be added to WSFC

Could you help us to understand what else we need to configure, in order to add Azure Node-3 to On-Premise WSFC ?


texts: INSERT Statement error message references column I am not including in query

texts:
I am using MSSQL 2008.

My INSERT statement is as follows:

INSERT INTO [DB1].[dbo].[SpotArtSong](ID)
SELECT  SNG.S_ID
FROM [DB1].[dbo].[SpotArtSong] SPOT
INNER JOIN [DB2].[dbo].[nt-art] ART
ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
INNER JOIN [DB2].[dbo].[nt-sng] SNG
ON ART.Artist_ID  = SNG.Artist_ID
AND left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE spot.BDSID is NULL

Column [ID] on [DB1].[dbo].[SpotArtSong] has some null values that I am trying to replace. I have found the replacements and when looking over the full column results, I am happy with what the search would return from SNG.S_ID. I am trying to insert that one column into the one column on [DB1].[dbo].[SpotArtSong].

Essentially, I scrapped some data into [DB1].[dbo].[SpotArtSong] and I am matching it to some existing production data split among [DB2].[dbo].[nt-art] (ART) and [DB2].[dbo].[nt-sng] (SNG). I know the data in ART and SNG is correct, I am correlating it with the scrapped data in SPOT

The failure statement says:

Cannot insert the value NULL into column 'Artist', table
'DB1.dbo.SpotArtSong'; column does not allow nulls. INSERT
fails.
The statement has been terminated.

I am not inserting anything there so I am not sure why it would fail. Plus, if I comment out the insert statement, the select statement does not return any NULL values.


texts: Can Oracle Named User Licenses be reused?

texts:
If I have 20 Named users for the month of January, then come February and those users do not retain access but are replaced by 20 new users.

Would I need 40 Named users for this use case or would 20 be sufficient?


texts: Suppress SQL Server Management Studio auto complete

texts:
Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a particular word so that it doesn't get in the way.

For example say I had the following query

SELECT Foo, Foo2 FROM SomeTable

As I type Foo and then hit space-bar SQL Server Management Studio's auto-complete kicks in and completes Foo to FooBar.


texts: How to ensure that two or more simultaneous requests to a MySQL table don't interfere

texts:
Suppose I have about a 1000 simultaneous requests to a table in my DB which consists of these statements (in order):
SELECT

INSERT or UPDATE (could be multiple)

DELETE

and I want to make sure that none of the requests interfere with one another and the table is in an updated state for the next incoming request. For example:
Request #1 comes in.

Request #2 comes in.

Request #1 starts to get processed.

Request #2 should start only when Request #1 is processed so that the data view is consistent and updated for the next request.

I searched for this situation around the web and found out two things that could be of help:

Transactions

Table Locking

But when I read about Transactions, I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row which defeats my purpose.
Also in case of locking, a column can only be locked in one mode i.e. either in READ or WRITE mode, which again fails to keep the data consistent as while reading a row it may get updated and the data which was just read would become an older version of what the table actually is right now.
Is there a solid way to achieve this which can make sure that no request interferes with another and the table is in an updated state for the next incoming request?
I am using MySQL with PHP if this helps.


texts: Is there any drawback in altering an existing column datatype from TEXT to VARCHAR(n)?

texts:
I have an existing column whose data type is TEXT. Now I realized that the values are just single strings. So, the data type VARCHAR(n) should have been proper in my case. So, I wanted to change the data type from TEXT to VARCHAR(n).

Now my question is:

I came to know that in case of TEXT data type, internally, the data will be stored in another place and a pointer will be saved in the table.
Now if I want to change the data type from TEXT to VARCHAR(n), will the data be copied into the table?

Basically,I am interested how data migration will take place internally and its possible side effect.

Note: The column is not used as index.


texts:
My MySQL 5.7 is running on UTF8

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

and for debugging puposes I switched the general log on

[mysqld]
general_log = on
general_log_file=/tmp/general.log

Now I'm seeing not correct encoded values inside general.log.

 file /tmp/general.log
/tmp/general.log: Non-ISO extended-ASCII text, with very long lines, with CRLF, LF line terminators

When searching for Rätsel (German Umlaut ä; ä) I can see inside logs  R�tsel (with broken Umlaut).

When having a look with an hexeditor, it appers like

27 2B 52 EF BF BD 74 73 65 6C 20 20 27

Heyedit shows it as  '+R...tsel  '

Now I'm wondering whether the default encoding of Log is might not UTF-8 (see file statement) or created query by application is wrong.


texts: Building friendship relatationship in mysql

texts:
I am creating friendship between user A and user B. So far my table looks like this:

friendship_request
| key:send_user | key:recv_user | sent |

friendship_request_history
| send_user | recv_user | sent | resolved_date | accepted | senderCanceled |

friendship
| key:user_1 | key:user_2 | date |

And when user 0 is friend with user 1 i put row for both sides:

| 0 | 1 | date |
| 1 | 0 | date |

However this requires additional space, when there are lots of user with friends.

Are there any better solutions, where i won't have to keep 2 rows for 1 friendship (e.g from both of sides )?

I could create just one row:

| 0 | 1 |
| 2 | 0 |

But here for example, if i wanted to insert new friendship for example for uses 0 and 1, and i would do this on 1 side -> so row would looks like:

| 1 | 0 |

I would first have to check if record | 1 | 0 | or | 0 | 1 | exists in databse, otherwise i could have duplicate. If would insert 2 rows ( for both sides ) i could just insert the row and catch Exception for duplicate entry.

Are there any tricks for this? THanks for help!


texts:
1. I have table UserData with following schema:

ID - bigint
UserID - bigint
Data - jsonb

2. Data column jsonb has following structure:

date - contain timestamp
text - contain description
photo_* - contains photo's link (sometimes absent in json)

3. Key photo_*:**

In photo_* key of jsonb Data field there are could be different values instead of asteriks (*), e.g.: photo_640, photo_1024, photo_1600

4. What do I need

I need to filter out only those rows in table UserData, that have any photo_* in jsonb Data column, i.e. Data field should contain key that starts with photo_


texts: "Keyword not supported: Server" error when using ADO.NET connection string to connect to Azure DB

texts:
I have an ASP.Net project that I am running. I have added a database to Azure, and Azure generated a connection string for the database. When running the application I however get an error stating that the server keyword within the string is not supported. Here is my connection string:

/**/
<add name="AnimalEntities" connectionString="Server=tcp:uuu.database.windows.net,1234;Initial Catalog=MyDataBaseName;Persist Security Info=False;User ID=xfakeID;Password=xxxfakePassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.EntityClient" />
/**/

I have analysed the answer on here but haven't been able to figure out what my eventual string should be.

How should I structure my string?


texts: How to change permissions to mysql pid file

texts:
By default mysql creates pid file as -rw-rw---- mysql:mysql mysql.pid. Is there any way to change those permissions? I need o+r for monitoring purposes.

Of course I could add sticky bit for directory above and change group ownership but if it could be changed somewhere in settings it would be the cleanest solution.

I'm running mysql 5.5 if that matters.


texts: How to restore symmetric key on the new database?

texts:
I have an issue with symmetric key restoring on the another database.
I have table in the first(old) database with encrypted column.(The table contains encrypted data)
The key in the old database has been created with the next sql script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTPSWD'
GO
CREATE CERTIFICATE [CertificateSecurity] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [EncryptKey] WITH
    ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE [CertificateSecurity]
GO

And now I have to move this table with all data to the new database.
I import table with data from the old database.
And there is a question: Is there any way to create the same encryption key in the new database without specifying key_source and the identity_value? (Because I don't have it)
I tried to create it with the same sql query as above, but decryption in this case returns NULL.

P.S. I know what I can decrypt data then recreate key(with all parammeters supplied) and encrypt data with a new key.But it would be great to find another solution.


texts: Problem with the function UNIX_TIMESTAMP

texts:
Query 1

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  SessionCallTime BETWEEN UNIX_TIMESTAMP('2013-08-01 00:00:00')
                           AND UNIX_TIMESTAMP('2013-08-01 23:59:59')
ORDER  BY SessionInfo.SessionCallTime DESC;

Query 2

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  ( SessionInfo.SessionCallTime BETWEEN '2013-08-01 00:00:00'
                                         AND '2013-08-01 23:59:59' )
ORDER  BY SessionInfo.SessionCallTime DESC;

What is the difference? Why does the first query give 0 rows and the second give records?

In this table there are 20000 rows between these two dates.


texts:
I installed Oracle 11g R2 PS4 on SunOS 5.10 sparc machine.

When i try to create a new database using the Database Creation Assistant tool (dbca) i get the popup message ORA-12560: TNS:protocol adapter error with no more explanations.
So i tried to create my database using the commandline but i got the same error :

SQL> startup nomount pfile="/opt/oracle/admin/TRIO/scripts/init.ora";
ORA-12560: TNS:protocol adapter error

I verified that the variables ORACLE_HOME, ORACLE_SID, ORACLE_BASE are all correctly set.


texts: how to change default language of SQL server management studio 2008R2?

texts:
I tried to change default language to french by using the below query.

USE ssidps;
GO
EXEC sp_configure 'default language', 2 ;
GO
RECONFIGURE ;
GO

but SELECT @@language gives "us_english" always.

My aim is to change my onshore system's default language from korean to english. All error/warning messages are coming in korean when query failed. And i couldn't catch that message as i developed in English.

Its working in the current session by the query "set language french" but not for the database.
I asked the same in stackexchange but i was referred to this site.
https://stackoverflow.com/questions/19654101/how-to-change-default-language-of-sql-server-management-studio


texts: We have copied a drive that had a db on it and I need to open the db on a new server

texts: Before I got here, they copied the old servers drive to an external disk.  Then they blew away the old server.  Now that they have a new server and SQL 2012 is installed, they would like to see the db that was on the old server.  How would I go about restoring the db thats copied to the external drive to the new server?  The old db is 2008 r2.


texts:
Given:

CREATE TABLE T
(  x int not null primary key
,  y int not null );

INSERT T(x,y)
VALUES (1, 0)
  ,(2, 0)
  ,(3, 0)
  ,(4, 0)
  ,(5, 3)
  ,(6, 0)
  ,(7, 4)
  ,(8, 0)
  ,(9, 0);

The requirement is to return rows (ordered by x) where y = 0, until a row is encountered where y is not equal to zero.

The expected result for the data above is to return the rows where x is between 1 and 4. The other rows where y = 0 should not be returned, because they come after the row where x = 5 and y = 3.


texts: Claiming disk space after removing table field

texts:
I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table.
Now we are starting to get out of server space because of the huge expanding size of this table.

I read that shrinking ,we do not want to loose the indexing of db because it was working fast for years and we do not want to get fragmentation expending.

We decided to delete that field and all its values:
Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?

I am attaching the db size query output to show you size expanding of last 5 months.


texts: Should data warehouse provide data for data composition for real time business transactions

texts:
As title, I always had a feeling that data warehouse should only support for data analysis or the like, but shouldn't be used in real time transactions. (eg. being a data source to provide a product info for a cart purchase about to happen).

Could anyone share any views?


texts:
My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site server and it seemed to be working great. The LSBackup job runs every 15 minutes.

I know that I need to change my maintenance plan to Full + Differential, but my question is do I need to remove all log shipping, restore the secondary database from the most recent full backup and then set up log shipping again?

Or, can I pause the LS jobs, to the restore and then restart the LS jobs?

Or, is the secondary server fine an I just need to fix my maintenance plan?


texts: optimize a 'having count (distinct ) ' query for big data

texts:
I have below query that I need to run on a table with 100million records but it's extremely slow (been running for 5 hours so far)

I am not sure how to optimize it, would be grateful for any help.

The table has an index on DID and week_no, and contains several other columns not indexed, and a primary key (id) indexed

DELETE FROM test
WHERE "DID" IN (SELECT "DID"
                FROM test
                GROUP BY "DID"
                having count(distinct week_no) < 4 );


texts:
We are running a series of Delete Queries to free up space on our SQL Server (2012)  I was able to recover files after the "Recovery Error", but it is very time consuming.

So I keep the size of my deletes to about 1 million records, and this seemed to prevent triggering the "Recovery Pending".

Is there a better approach?

Thanks much


texts: Migration of MariaDB/MySQL to another server without downtime

texts:
System : MariaDB (MySQL 5.5)

Parameters : innodb_file_per_table=1

The database I have now is approaching its full capacity (94% full now, 6.4TB out of 6.9TB)

I can't run OPTIMIZE TABLE to reclaim space as there is insufficient space to hold the temporary tables.

I have prepared another new server to migrate the bulk data over. However, I do not wish to have any downtime during this period.

Hence, I attempted to do the following :

1) In the most utilized and huge tables, I began to delete old/unnecessary records to withstand the incoming insertions (since by DELETING records, MySQL will reuse space)

2) Started migrating of the data over by running a script that queries the records and insert them into the new server by doing batch inserts (every 5000). The reason why I am running a script is because the database schema different in the new server (I sharded the table).

Problems encountered:

1) The insertion rate of the incoming records is faster than the deletion rate, which means my .ibd table is still growing

2) The batch insert migration script is still too slow and it definitely can't finish in time before the original database server gets full (almost 4TB+ data to migrate)

I can most probably run more migration scripts in parallel, and see if I can delete the old records at a faster rate.

However, is there any alternative approaches I can take or improvements to do?

Edit : The application currently points to the old and new server, and after every a batch of complete migration of records, I'll update the application such that it'll connect to the new server instead. The storage is not a SAN, but directly connected. As we ran out of disks ports, we can't add more to the local storage. Also, we do not have regular backups (a huge mistake at our end).


texts: Using a select max(blah) as an input parameter

texts:
This may be a naive question, but is it possible to use a select max(blah) to update a parameter in an input statement?
input a,b,c into table1 values (10, 20, (select max(c) where a=10) + 1) for example?
I tried this in postgresql and got the hint
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', now(), 12.4, (select max(version) where nmi = 'E1234' and interalend = now()) + 1)

ERROR:  column "version" does not exist
LINE 2: ('E1234', now(), 12.4, (select max(version) where nmi = 'E12...
                                           ^
HINT:  There is a column named "version" in table "data", but it cannot be referenced from this part of the query.
SQL state: 42703
Character: 105

I'm not even sure if this is possible. The idea is that if the entry already exists in the table, I need to add the new data and update the version by 1.
Hope this makes sense.
Thanks to the help here, I've got this working now IF there is a value in version to start with, however, if there is no value yet, it sets to null and so doesn't increment on subsequent calls. I've tried setting a default value on that column to 1 (thought that would have done it, but no), and tried a few different ways unsuccessfully to get the query something like
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', '2021-02-08 00:00:00+11', 12.4, (select max(d.version) from "MeterData".data d where d.nmi = 'E1234' and d.intervalend = '2021-02-08 00:00:00+11') into value, case value==null then 1, else value+1 end))

but that just ends badly.
Is there a way to do this, or do I need to write a function to handle this (or do it in some other code external to the db?
I have no idea why this question was closed.. what the??? How is this not a relevant question?


texts: Finding the nearest geo points across two tables?

texts:
I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M.

Along with the lat/lon columns I have created a geo-indexes for both tables in this way:

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);
UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

To find the nearest points from the table with 80M respect to 50M within 0.1 miles I run something like:

   SELECT A.latitude, A.longitude, B.latitude, B.longitude,
   FROM my_table_50 AS A, my_table_80 AS B
   where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10
   ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;

The query is very slow to run (pretty much is cartesian 50M X 80M).

Is there a way to speed it up?

Also, is it really useful to use "postgis geo indexing" for a problem like this?
Using "pythagorean theorem" can be enough (like in the chosen answer here https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem), as I am expecting distances way far shortest than earth radius or it may lead to some error?


texts: Is there a use case where RDBMS is the best data store for querying only?

texts:
I am trying to figure out whether there is any legitimate use case for Relational Data Store (like MySQL, PostgreSQL, etc) in DDD, Event sourcing and its projections. Hear me out.

In event sourcing your OLTP and write side is solved for you. For OLAP and the whole read/query side, you are free to create a projection - store data from the source of truth in a way that will fit your query use case the best way possible. So that the read is extremely fast. Therefore I am trying to figure out, if I am only to query the data store, is there a use case, that I would want to store in RDBMS/Relational Data Store? I see that for any use case there is a better Data Store than Relational.

Do you need to get one item by ID? - key-value Data store is the best
Do you need some nested structures to query? - Document Data store is the best
Do you want to query relations between structures? - Graph Data store is the best
Do you want to do some analytical processing? Aggregations, averages and what not? - Column Data store is the best.

Is my thinking correct? Do you see any flaws? Read use cases that RDBMS solves the best?


texts:
I have a query which contains a subquery. When executed on a local copy of my database it runs as normal and executes within < 1 second.

I've noticed however that if I run this same query on our server the inner query behaves like a correlated subquery, taking around 90 seconds, and the predicate applied to a clustered index scan for data which relates to the subquery appears to be incorrect - it contains the alias used in the outer query, even though all aliases used in the inner query are different, and instead of 1 execution for the index scan it shows hundreds.

I've noticed that this behaviour only applies when the query executes serially - if I lower the cost threshold for parallelism on the server and it goes parallel then this behaviour does not appear and the query runs correctly. Notably, the query runs correctly in my local environment whether serial or parallel, and the predicate applied there looks fine.

Both the local environment and the server are running SQL Server 2016 SP1 CU7 (Enterprise on the server, Developer locally), and I've looked at the different SET options and verified that they are using the same compatibility level (SQL 2012).

Is there anything that would explain the difference of behaviour here?

The problem is not that the plan is different, it's that it's not a correlated subquery and is being executed as one - the inner query is incorrectly referencing the outer query when the query executes serially, but not when it goes parallel on the same server.

The server is set to 2012 compatibility mode because when we upgraded, the new cardinality estimator caused some problems with a few queries and we haven't gotten around to fixing those yet so have remained on 2012 compatibility mode to use the legacy cardinality estimator.

All 4 plans can be viewed here.

Looking at the query and then at the clustered index scan on the deposit payment table, this shouldn't be using the predicate it is using as it references the outer query, which unless I'm misunderstanding, is incorrect. It also shouldn't be executing that operator the 200+ times that it is.

On the same server, if I lower the cost threshold for parallelism so that it goes parallel, it applies a different predicate to that operator where it does not reference the outer table and does not behave like a correlated subquery. This is the same behaviour I see if I run the query locally (either running serially or parallel).


texts:
We are using SQL Mirroring as an HA solution and Log Shipping as a DR solution.

Recently, the mirror database has been configured as a log shipping primary (the mirror/primary database) to allow log shipping to continue after database mirroring fails over, using the same configuration we used for the primary on the principal database.

Now the problem is every so often the restore job fails due to:

  Exclusive access could not be obtained because the database is in use.

This is because two restore jobs (one from principal/Primary, and one from the mirror/primary) start at the same time. I have changed the job schedules to be run at different times, however.

For example, the restore job from the principal/Primary is scheduled for every 13 minutes and the restore job from mirror/Primary is scheduled for every 10 minutes. But there are still some times that they start at the same time.

Is there any way to prevent this?


texts:
Is it possible to capture output of table-valued function? For example, function is defined as follows.

CREATE FUNCTION [dbo].[ufn_MyFunction]
(
    @ItemId INT
)
RETURNS @ReturnTable TABLE
(
    RowId int primary key NOT NULL,
    Value nvarchar(250) NOT NULL
)
AS
BEGIN
    -- ...
    RETURN;
END

I understand you can do SELECT * FROM ufn_MyFunction(@id), but I need to use values from every row of the returned table. Is there a way to achieve this?


texts: Are Snapshot files created during MS SQL replication one time use only?

texts:
I added a new article in an existing publication.

I saw that it generated a new snapshot folder (E:\Database\Snapshots\unc\S749TCAPDB01_NFS_XXXX_XXXX_PUBLICATION\20190417114556).

But this folder only contains a single article definition which I added previously.

It makes me wonder if this snapshots folder are only one time use and can be deleted afterwards


texts: Mariadb on Centos 7 not changing max connections based on my.cnf

texts:
The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#symbolic-links=0

#vish start
open_files_limit = 8000
max_connections = 500
#set-variable=max_connections=500
thread_concurrency = 8
#concurrent_insert=2
thread_cache_size=2000

interactive_timeout=180
wait_timeout=180

max_allowed_packet = 32M
key_buffer_size = 3000M
read_buffer_size = 16M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 256M
myisam_repair_threads = 1
#myisam_recover = 4
max_heap_table_size = 2048M
tmp_table_size = 1024M
table_open_cache = 2000
table_cache = 2000
sort_buffer_size = 128M
join_buffer_size = 128M
query_cache_size = 128M
query_cache_limit = 128M

#slow_query_log=1
log-slow-queries
long_query_time=2
#log_queries_not_using_indexes=1
slow_query_log_file = /var/log/mariadb/host_name-slow.log

[mysqldump]
user=root
password=*********
#max_allowed_packet = 1024M

[myisamchk]
key_buffer_size = 2500M
sort_buffer_size = 1024M
read_buffer = 32M
write_buffer = 32M

[innodb]
innodb_buffer_pool_size=3G
innodb_buffer_pool_instance=2
innodb_additional_mem_pool_size=20M
innodb_log_file_size= 512M
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=16
#innodb_read_io_threads=16
#innodb_write_io_threads=16
#innodb_io_capacity=2000
#innodb_lock_wait_timeout=120

innodb_flush_method=O_DIRECT
#vish end

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#!includedir /etc/my.cnf.d


texts:
We currently have an issue with some queries/jobs causing non-yielding at a specific point in time.
There no are mini dumps which are generated when this happens.
Is there a way to know which query was causing the non-yielding on a specific scheduler in the past?


texts: Suddenly cannot access SQL Server database

texts:
My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below:

It cannot be detached and attached. I need to 'Take Offline' and then 'Bring Online' to solve it. This problem happens around once a week.

After checking the error logs, the following are the records:

  Error: 18456 Severity: 14 State: 38.
  FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.
  OS error: 32(The process cannot access the file because it is being used by another process.).
  Error: 17204 Severity: 16 State: 1.
  Starting up database 'PWRCMS'.
  Login failed for user 'sa'.
  Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
  Error: 18456 Severity: 14 State: 38.
  Login failed for user 'sa'.
  Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
  Error: 18456 Severity: 14 State: 38.
  FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWLCMS.mdf for file number 1.
  OS error: 32(The process cannot access the file because it is being used by another process.).
  Error: 17204 Severity: 16 State: 1.
  Starting up database 'PWRCMS'.
  Starting up database 'PWLCMS'.

Is there any solution to prevent this happening?


texts: Normalize End Date - Nulls or separate table

texts:
I was hoping for some advice on best practice in schema design - as far as End Date values are concerned.

In this case it is for a Suppliers table - we are required to store information on the Start Date and - should the relationship finish the End Date.

In an environment with low Supplier turnover - I can see the argument for allowing a nullable end date column in the Supplier Table.

Conversely, in a high turnover environment for pushing SuppierID and EndDate into a separate SupplierEndDate table.

The majority of articles I have read - have concluded that it should be judged on a case by case basis. However, I tend to lean more toward creating SupplierEndDate Table.

Is there a definitive best practice for OLTP schema or at least a favoured approach?


texts: Postgres ignoring GIN index for LIKE query?

texts:
I am working in Postgres 9.4. I have a table and index like this:

                                              Table "public.title"
             Column              |          Type           |                     Modifiers
---------------------------------+-------------------------+----------------------------------------------------
 id                              | integer                 | not null default nextval('title_id_seq'::regclass)
 pty_addr                        | character varying(1000) | not null

I have a GIN index on the pty_addr column:

"title_pty_add_15f367_gin" gin (pty_addr)

My query is this:

EXPLAIN ANALYZE SELECT * FROM "title" WHERE  pty_addr LIKE '%FOO%';

This query is performing a sequential scan, taking 1.2 seconds:

                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on title  (cost=0.00..193271.60 rows=599 width=4446) (actual time=1.191..1220.640 rows=7945 loops=1)
   Filter: ((pty_addr)::text ~~ '%FOO%'::text)
   Rows Removed by Filter: 3592055
 Planning time: 0.330 ms
 Execution time: 1220.992 ms

Why isn't this query using the index?


texts:
In database context, I have come across horizontal scalability as one of the advantages of the NOSQL databases. What does the term mean?

CouchDB on Wikipedia
NoSQL on Wikipedia

How would it compare to vertical scaling?


texts: How to structure a model to properly and efficiently represent tree-like data on relational databases?

texts:
Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical implications?

I'm assuming that the RDBMS has not special features to handling that other than regular SQL ANSI or common available features.

In doubt I'm always interested on MySQL and PostgreSQL and eventually SQLite.


texts: Carry over long sequence of missing values with Postgres

texts:
I have a table like this:

create table foo (foo_label text, foo_price int, foo_date date);

insert into foo (
          values
          ('aaa', 100,  '2017-01-01'),
          ('aaa', NULL, '2017-02-01'),
          ('aaa', NULL, '2017-03-01'),
          ('aaa', NULL, '2017-04-01'),
          ('aaa', 140,  '2017-05-01'),
          ('aaa', NULL, '2017-06-01'),
          ('aaa', 180,  '2017-07-01')
        );

As you can see a few values on the foo_price column are missing.

What I need is that missing values get filled up with the "previous" available value in this way:

 foo_label | fixed_foo_price | foo_date
-----------+-----------------+------------
 aaa       | 100             | 2017-01-01
 aaa       | 100             | 2017-02-01
 aaa       | 100             | 2017-03-01
 aaa       | 100             | 2017-04-01
 aaa       | 140             | 2017-05-01
 aaa       | 140             | 2017-06-01
 aaa       | 180             | 2017-07-01

My attempt:

select
    foo_label,
    (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,
    foo_date
from (
  select
      foo_label,
      lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price,
      foo_price,
      foo_date
      from foo
) T;

As you can see from here:

https://www.db-fiddle.com/#&togetherjs=s6giIonUxT

It doesn't fill completely the '100' series.

Any idea how can I get the wanted result?


texts:
I'be build a Tabular Cube with FactTable and four dimensions. Fact table has about 2.500.000 of rows (and about 20 calculated measures on the cube level) and dimension tables from 5k-50k of rows. When I connect to the cube from Excel and try to add dimension I have to wait like 15-20 seconds for this. Do you know some ways to improve the query performance of such tabular cube (SQL 2014) ?

Performance issue not visible (Year consolidated), filtering by Port works fast:

Performance issue visible (Year expandeding to weeks takes about 15 sec, filtering by Port after next 15 seconds everytime):


texts: Replacing the MS-Acccess frontend for accessing linked tables with SQL Server?

texts:
Is it possible to use SQL Server / SQL Server Management Studio to do queries across linked tables the way that you would do them in MS-Access?

The MS-Access front-end is frustrating and time consuming to say the least, which has led me to search for alternatives.  I started using SQuirreL SQL, and was quite pleased with it, but it doesn't have the linked table functionality so you're somewhat limited on the queries that can be run.

Also, can you provide me with suggestion for a tutorial or book on how to set something like that up?