texts:
let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, plus some other columns.

My question is does it make any sense to make the database columns in tbl_children like parent_id first, following by child_id? Because I will index them, and my WHERE clause should be some thing like WHERE parent_id = x AND child_id = y.

Again this is just an example. I just want to know if column orders could improve the performance on indexed columns or not?

Platform: MySQL, InnoDB, on Integer Values.


texts:
What ways are there to do version control of an MS Dynamics database? I've found mentions of DACBAC, and obviously SSIS packages could just be dumped in a git repo.

I'm particularly keen on learning how to keep connections between different environments (no one wants to copy an SSIS over and have to go through each connection updating it).

Additionally, and this may be a "novice" question, but my understanding is that dacbac doesn't contain SSIS. Is this correct? Would both need to be in source control?

What is the best way!?


texts: Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable

texts:
I have a set of starting and ending times that I need to store in an Oracle database.  I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where a time value, such as 9:30AM, falls in between the start time and end time).  In SQL Server I would just use a TIME datatype field, but Oracle does not appear to have an equivalent.

I have seen a number of blogs and forums that recommend just using a DATE field and doing conversions with TO_CHAR then comparing, or to store the time values as varchar(4 / HHMM) or varchar(6 / HHMMSS) fields.  Both of these seem needlessly inefficient.

Is there a better or more efficient way to accomplish this in Oracle?


texts: Matching a column from a table to another table

texts:
I have 2 tables in a Oracle database and if I do:

SELECT ID,ID_EXTERN_FOR_JOB FROM SOL_PUBLIC.SOL_SUBSOL_CHESTIONARE@RACDB
WHERE ID=1207;

I get:

ID      ID_EXTERN_FOR_JOB
1207    -1

On the other table, if I do:

SELECT ID,ID_EXTERN_FOR_JOB FROM SOL.SOL_SUBSOL_CHESTIONARE
WHERE ID_EXTERN_FOR_JOB=1207;

I get

ID      ID_EXTERN_FOR_JOB
2009    1207

And I want to find out all the rows that don't match this "rule" of having the ID_EXTERN_FOR_JOB from SOL in ID FROM SOL_PUBLIC and I don't know how to do that. Thanks!


texts: MySQL: Optimizing for large but discrete data sets

texts:
In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). Updating the table gets slower and slower as it gets bigger. I can deal with a couple of million rows from a single agency, but what happens when I add 10 more feeds? 50?

Now, the data sets are completely independent of one another. I won't be trying to join information across DART, MTA, and Transport for London. I feel like it would be very bad database design, but I'm tempted to create a separate table for each and forget about the whole thing.

I'm sure this has been answered somewhere, but I really don't know what I'm searching for. I've read up a bit on partitioning, but I'm not sure if that will solve my problem. Would adding a hash partition on my agency_id field solve issues with exploding BTREE indexes?

Here's my current table structure:

CREATE TABLE `stop_times` (
  `trip_id` bigint(20) unsigned DEFAULT NULL,
  `arrival_time` time DEFAULT NULL,
  `departure_time` time DEFAULT NULL,
  `stop_id` bigint(20) unsigned DEFAULT NULL,
  `stop_sequence` smallint(5) unsigned DEFAULT NULL,
  `stop_headsign` tinytext,
  `route_id` mediumint(8) unsigned DEFAULT NULL,
  `feed_id` smallint(5) unsigned DEFAULT NULL,
  `update_id` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `stop_sequence` (`trip_id`,`stop_sequence`) USING HASH,
  KEY `trip_id` (`trip_id`) USING HASH,
  KEY `departure_time` (`departure_time`) USING BTREE,
  KEY `stop_id` (`stop_id`) USING HASH,
  KEY `feed_id` (`feed_id`) USING HASH,
  KEY `update_id` (`update_id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks in advance for the help.


texts: MariaDB on Windows Server 2012 R2 - out of memory

texts:
I have two servers, one with Windows Small Business Server 2008 and the other runs Windows Server 2012 R2. The servers have running multi master replication. Both uses MariaDB 10.2.6. On the old 2008 everything works fine, even that the database is 32-bits. But on the 2012 I get frequently "Out of memory", the database uses only ~450MB and the system have 6GB free memory when the error shows up. Here is my complete my.ini, I tried already everything:

[client]
# password       = your_password
port            = 3306
socket          = "D:/OCS/MariaDB/mysql.sock"
plugin-dir      = D:/OCS/MariaDB/lib/plugin

# Here follows entries for some specific programs

# The MySQL server

[mysqld]
tmp_table_size=128M
performance_schema = off
default-storage-engine = myisam
skip-innodb
skip-performance_schema
local-infile
query_cache_type = 1
query_cache_limit = 10M
query_cache_size = 128M
port= 3306
socket = "D:/OCS/MariaDB/mysql.sock"
basedir = "D:/OCS/MariaDB"
tmpdir = "D:/OCS/MariaDB/tmp"
datadir = "D:/OCS/MariaDB/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
event_scheduler = ON
server_id=1
log-basename=2
log_bin=mysql-bin
max_binlog_size=64M
max_relay_log_size=64M
relay_log_space_limit=64M
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors=1062
# Change here for bind listening
# bind-address="127.0.0.1"
# bind-address = ::1          # for ipv6

# Where do all the plugins live
plugin_dir = "D:/OCS/MariaDB/lib/plugin/"

innodb_data_home_dir = "D:/OCS/MariaDB/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "D:/OCS/MariaDB/data"
#innodb_log_arch_dir = "D:/OCS/MariaDB/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="D:/OCS/MariaDB/share/charsets"

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
max_execution_time = 5000
local-infile
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

I am running out of ideas why this happens.. I have tested if the system have some limitations but a small python program allocates over 2GB of RAM with no problems. Both servers are running the same config - on the 2008 the memory usage goes up to 1.5GB with no problems.


texts: what is the immediate action to be taken from a Mongodb dba when we come to know our data is compromised or open to public without our knowledge.  what are the actions to be taken immediately so that we dont damage more of our client data which is the must most important?


texts: Database Design - Custom Attributes / Columns

texts:
So I'm trying to figure out the best way to go about handling this database design wise. This is a rough idea of how I THINK it should go, but not really sure.

If I have orders that have their own necessary attributes that a user can create for all of their orders to have, and if the order can be comprised of items, and those items also can have their own custom attributes, and not necessarily the same attributes as the order, what makes the most sense?

Orders
======
id
client_id

Items
=====
id
user_id
name

Attributes
==========
id
user_id
is_order_attr
is_item_attr
name
type

Orders_Attributes
=================
id
order_id
attribute_id
value

Items_Attributes
================
id
item_id
attribute_id
value

A) Have an Attributes table for Orders and an Attributes table for Items, subsequently storing the values in another table, say Orders_Attributes_Values and Items_Attributes_Values?

B) Storing everything in a JSON Data Type column in some type of Orders_Configs table and then storing the values in additional tables with the JSON Data Type?

or

C) Something completely different?


texts: Assigning a domain user to run MSSQLSERVER service 2008R2

texts:
I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server

I want a low level domain user to run the MSSQLSERVER service so that I can enable backup to a remote network location, this is working fine on my other SQL Server 2008R2 instance.

I have a domain user called fred@mydomain I have added this user to the server local administrator group and the group SQLServerMSASUser$**MYSERVER**$MSSQLSERVER. This is the group I believe is the one that allows members to run the MSSQLSERVER service

I have created the login in SQL Server for the user and added him to all the server roles but dbcreator and mapped it to all the instance databases as a public database role

HOWEVER when I go to SQL Server Configuration Manager to change the MSSQLSERVER service and selected the fred@mydomain and press apply I get a log on error.

Can anyone suggest what I might have missed please ? I have used Here as my reference


texts:
Recently I have developed software using SQL Server 2012. Now when I try to connect, I get this error message:

  Cannot connect to AAFI\SQLEXPRESSA network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)


texts: Speeding Up Query - Hangs at Sending Data - Indexed Correctly

texts:
I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, and EMAIL on the temp_1 table.

I also have indices on first_name, last_name, unit_number, email_address, street_address_1, street_address_2, city, state, and postal_code of the temp_current_new_clients tables.

Would it help to right the logic for the Confidence_Code variable in a function? Would that help my querying time?

Also, I currently do not have primary or foreign keys on the two tables in question. Would keys speed my query? Thank you.

SELECT
    a.PID AS PID,
    a.NID AS NID,
    a.UNIT_PARENT AS UNIT_PARENT,
    a.FIRST AS FIRST,
    a.MIDDLE AS MIDDLE,
    a.LAST AS LAST,
    a.HomeAddressLine1 AS HomeAddressLine1,
    a.HomeAddressLine2 AS HomeAddressLine2,
    a.HomeCityName AS HomeCityName,
    a.HomeStateAbbreviation AS HomeStateAbbreviation,
    a.HomeZipCode AS HomeZipCode,
    a.HomeZip4 AS HomeZip4,
    a.EMAIL AS EMAIL,
    a.Date_Log_Updated AS Date_Log_Updated,
    a.Log_Row_ID AS Log_Row_ID,
    b.personal_party_id AS Personal_Party_ID,
    8 * (LOWER(a.EMAIL) LIKE LOWER(b.email_address)) +
    (LOWER(a.HomeAddressLine1) SOUNDS LIKE LOWER(b.street_address_1)) +
    (LOWER(a.HomeAddressLine2) LIKE LOWER(b.street_address_2)) +
    (LOWER(a.HomeCityName) LIKE LOWER(b.city)) +
    (LOWER(a.HomeStateAbbreviation) LIKE LOWER(b.state)) +
    (LEFT(a.HomeZipCode,5) LIKE LEFT(b.postal_code,5)) +
    ((LOWER(a.FIRST) LIKE LOWER(b.first_name)) AND
    (LOWER(a.LAST) LIKE LOWER(b.last_name)) AND
    (a.UNIT_PARENT LIKE b.unit_number)) AS Confidence_Code,
    b.ingest_date AS Match_Date
FROM
    temp_1 AS a
INNER JOIN
    temp_current_new_clients AS b
ON
    a.FIRST = b.first_name
    AND a.LAST = b.last_name
    AND a.UNIT_PARENT = b.unit_number;


texts: Mysql - Opitmizing a range query with group by

texts:
I have a table with the following definition:

CREATE TABLE `sales` (
  `saleId` varchar(100) NOT NULL,
  `orderId` varchar(100) DEFAULT NULL,
  `shopId` varchar(100) DEFAULT NULL,
  `fulfillableQuantity` int(11) DEFAULT NULL,
  `fulfillmentStatus` varchar(30) DEFAULT NULL,
  `grams` double DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `productExists` tinyint(1) DEFAULT NULL,
  `productId` varchar(100) DEFAULT NULL,
  `variantId` varchar(100) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `requiresShipping` tinyint(1) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `taxable` tinyint(1) DEFAULT NULL,
  `title` text,
  `totalDiscounts` double DEFAULT NULL,
  `variantTitle` text,
  `cogs` double DEFAULT NULL,
  `customerEmail` varchar(100) DEFAULT NULL,
  `discount` double DEFAULT NULL,
  `processedAt` varchar(100) DEFAULT NULL,
  `shippingCost` double DEFAULT NULL,
  `rowStatus` varchar(10) DEFAULT NULL,
  `processedAtNano` bigint(20) DEFAULT NULL,
  `lastUpdated` bigint(20) DEFAULT NULL,
  `productType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`saleId`),
  KEY `i_shopId_nano` (`shopId`,`processedAtNano`),
  KEY `i_sales_orderid` (`orderId`),
  KEY `i_sales_variantid` (`variantId`),
  KEY `date_and_product_index` (`shopId`,`processedAtNano`,`productId`,`rowStatus`),
  KEY `i_sales_custemail` (`customerEmail`) USING BTREE,
  KEY `i_sales_test_index` (`shopId`,`processedAtNano`,`rowStatus`,`customerEmail`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and I'm trying to run this query:

SELECT
    get_total_freq(MAX(s.processedAtNano),
        MIN(s.processedAtNano),
        count(s.customerEmail)) AS a
FROM
    sales AS s
WHERE
    s.shopId = 'p2k-c5k-o100k'
    AND s.processedAtNano < 1525132799999
    AND s.customerEmail IS NOT NULL
    AND s.customerEmail != ''
    AND s.rowStatus = 'ok'
GROUP BY
    s.customerEmail
HAVING
    count(s.customerEmail) > 1)

but this just won't use the indexes and would start using where and filesort

below is the explain for this query:

1|"SIMPLE"|"s"|"ref"|"i_shopId_nano,date_and_product_index,i_sales_test_index"|"i_shopId_nano"|"103"|"const"|277686|"Using index condition; Using where; Using temporary; Using filesort"|

I have experimented with all logical permutations of indexes that I can think of but still, the query time is 11sec, while without the group by its 630ms

definition of get_total_freq:

CREATE FUNCTION `get_total_freq`(max BIGINT, min BIGINT, no_of_orders INT) RETURNS double
begin
    declare value double;
    set value = DATEDIFF( from_unixtime(floor(max/1000)),
from_unixtime(floor(min/1000)) )/ (no_of_orders-1);
    RETURN value;
end

UPDATE

Solution I ended up using thanks to both Rick James and jonggu as it was a mix of both

The final index that I created

"i_sales_stack" => "shopId,rowStatus,customerEmail,processedAtNano"

And the final query I'm using

SELECT
    avg(b.total_freq)
FROM
    sales AS s, (
        SELECT
            saleId,
            get_total_reorder_freq (MAX(a.processedAtNano),
                MIN(a.processedAtNano),
                count(a.customerEmail)) AS total_freq
        FROM
            sales AS a
        WHERE
            a.shopId = 'p2k-c5k-o100k'
            AND a.rowStatus = 'ok'
        GROUP BY
            a.customerEmail
        HAVING
            count(a.customerEmail) > 1) AS b
    WHERE
        s.saleId = b.saleId
        AND s.processedAtNano < 1531440000000
        AND s.customerEmail != ''


texts: Firebird database performance after server upgrade/restart

texts:
Got a 350 GB database (more than 40M records plus 0 - 1000 BLOBs for each record in another table). After upgrading Firebird to version 2.1.5 (mainly because of filesystem cache issue) database became terribly slow, both insertion and fetching. How to restore performance? I tried running some queries to force caching, it was somewhat helpful, and currently left it with gbak running, but what's the"proper" way?

UPDATE:
Everything went back to normal in 24 hours or so, but the question is still relevant.


texts:
Can any body please explain how Oracle DB handles the client request ? is it creates new user process for every connection to db ?

I had look in oracle docs, seems there is concept 'Dedicated and Shared Server process'  this was not clear to me. Oracle DB experts please help


texts: Difference in MYSQL Master Slave DISK SPACE

texts:
I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is consuming relatively more space than master. Even the mysql.conf is same for both master and slave. Any idea why this might happen or any leads that i can check?
Server            Name           Engine Version Row_format  Rows    Avg Data_length Max Index_length    Data_free
Master            MasterTable       InnoDB  10  Compact 148087092   320 47392686080     0   31006982144 6291456
Slave(10.52.146.189)    SlaveTable  InnoDB  10  Compact 214554824   476 102218530816    0   40179859456 5242880


texts: listener does not currently know of SID given in connect descriptor in oracle 10g and solaris 10 env

texts:
I have an Oracle 10g database on a Solaris 10 system. The SID=OTRS, I can connect to the Oracle database from the local server itself. But I am not able to connect from another client. It is returning an error

ORA-12505 TNS:listener does not currently know of SID given in connect descriptor

If I execute the command show parameter listener, it shows

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_OTRS
remote_listener                      string

But my listener.ora file is

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = otrs)
      (SID_NAME = otrs)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_3)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

and my tnsnames.ora file is

otrs =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ticket)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = otrs)
    )
  )

CONNECT_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID= otrs)
      (PRESENTATION = RO)
    )
  )

If I run the command lsnrctl services , It shows

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 27-DEC-2012 10:42:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TICKET)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

The listener is not recognizing the sid OTRS. What is solution to resolve this error?


texts: Migration of Always On Availability Group from old to new SAN within same data center

texts:
What are the steps required to move AAGs (version=SQL 2012 and OS=Windows 2012 R2 O) to new SAN within same data center?

It is a virtualized environment.

Instead of building new servers - can the VM and storage be v-motioned to new storage?


texts: SQL Server data, logs, backups files in mounted foldersin C:\

texts:
I know that is a good practice to have data, logs and backups files of SQL Server in different disk, for example

D:\ for Data files
E:\ for Logs files
F:\ for Backup files

If I want to use this disks mounted in folders in C:\is safe?
For example C:\MSSQL\Data, C:\MSSQL\Logs, C:\MSSQL\Backups respectively...


texts: Deduplicate SELECT statements in relational division

texts:
I have a query that does a lot of duplicate work:

SELECT visitor_id, '1'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
             WHERE key = 'utm_campaign' AND value = 'campaign_one')
  AND id IN (SELECT event_id FROM params
             WHERE key = 'utm_source'   AND value = 'facebook')
GROUP BY visitor_id

UNION ALL

SELECT visitor_id, '2'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
             WHERE key = 'utm_campaign' AND value = 'campaign_two')
  AND id IN (SELECT event_id FROM params
             WHERE key = 'utm_source'   AND value = 'facebook')
GROUP BY visitor_id

As you can see, it performs different filtering on the params table 4 times. I'm using Redshift and although it scans this table very quickly, I have quite a few of these statements UNIONed together. Is there a way to rewrite the SQL using CASE/IF statements?

The example uses key = 'utm_source' AND value = 'facebook' in both, but this is not necessarily true for all selects.


texts: Sql Server Stored Procs vs Linq with C# and unit testing

texts:
I'm going to run into a situation where I am, where the stored proc vs Linq is going to come up. I know I'm asking this in the DBA stackexchange so most of you might say use stored procs, but I'm curious on the unit test suites that exist in SQL Server 2012.

Visual Studio 2015 has a test explorer part of the IDE and MSTest framework which makes writing unit tests really easy, and honestly a lot of "business" logic can be done in SQL/Linq so that's the part that needs to be tested most often.

I'm open to using stored procs but coming from C# my expectation is:

1) Be able to mock some data up just for said tests (in memory not disk for both speed and to make sure it always stays around with the test vs someone deleting it or moving it sometime in the future)

2) Call the proc against mocked data, and then compare the results and get some nice visual feedback that the test failed/succeeded.

3) Seeing all unit tests listed out nicely in some kind of Test Explorer and able to run them all with a click of a button or have them grouped in various ways.

My question is, does this or something similar to this exist on Sql Server to help aid in unit testing stored procedures?


texts:
I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.
Each time during the error, the primary key of SERIAL increased.
After fixing the bug, the table measurements is

 measurement_id | measurement_size_in_bytes |             time
----------------+---------------------------+-------------------------------
              1 |                     77777 | 2015-07-14 18:29:56.858703+03
              2 |                       888 | 2015-07-14 18:29:56.882552+03
              3 |                       888 | 2015-07-14 18:30:15.505957+03
              4 |                       888 | 2015-07-14 18:41:01.878106+03
             39 |                     77777 | 2015-07-15 12:11:21.21391+03
             40 |                     77777 | 2015-07-15 12:11:59.551973+03
             41 |                     77777 | 2015-07-15 12:12:05.48982+03
             42 |                     77777 | 2015-07-15 12:13:02.402053+03
             43 |                     77777 | 2015-07-15 12:13:02.419412+03
             44 |                       888 | 2015-07-15 12:13:02.434728+03
         ...

where the amount of error statements has been 35 (= 39-4).
This jump in IDs may become a challenge later when I want to integrate this table with a partial index where order matters.

Table

CREATE TABLE measurements
    (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL,
        time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time)
    );

What are the challenges of keys with gaps?


texts: SQL Server 2019 standard edition CDC with SSIS standard edition

texts:
The primary question is:

Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise Edition?

Related questions are:

Is there a manual process to do what the SSIS CDC Control Tasks automagically do?
OR - is there some other performant way (i.e. not using triggers) to sync data changes from SQL Server Standard Edition out to a non SQL Server cloud database?  Log shipping does not appear to be an option.

The background is:
After seeing that Microsoft enabled CDC in the standard editions of SQL Server 2016 sp1 and above, we built a SSIS package based on that to sync data from an on premise database out to a postgres database in the "cloud".
In testing from Visual Studio, everything works fine, but when we deploy the package to our SQL Server 2019 standard edition server, we run into an error that states:

The task "Mark Initial Load" cannot run on the installed edition of Integration Services. It requires Enterprise Edition (64-bit) or higher.

So it appears that while you can enable CDC on SQL Server standard editions, you can't actually make use of Microsoft's other tools to get any benefit from it without paying for enterprise edition anyway.


texts: To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?

texts:
Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily on the version.)

Observation: Clearly, I can use the the MySQL JDBC driver (Connector/J) or the MariaDB JDBC driver (MariaDB Java Client) to make the connection to the MySQL server. One huge benefit from the standpoint of an ISV is that the MariaDB driver is LGPL licensed while the MySQL driver is GPL licensed. Performance of the MariaDB driver seems very good.

Question: To what extent is the MariaDB JDBC driver a perfect replacement for the MySQL JDBC driver?

I'm not looking for a debate on the relative merits of the two. But rather I'm hoping someone can answer along the lines of one of these:

The MariaDB JDBC driver has important issues with X and Y, so you'll have troubles if you use either of these.
Based on some article or specification or code or other evidence, the MariaDB JDBC driver fully implements [at least] all methods available in the MySQL driver. You'll be fine.


texts: The relations between sessions, DOP and sets of parallel execution servers

texts:
From How Parallel Execution Works:

  The number of parallel execution servers in one set is the degree of parallelism (DOP)

What is the meaning of "one set" in the document? Does it have a relationship with user session? My understanding is that one session has only one set, two sessions have two sets, and the total session's execution servers is limited by parallel_servers_target. Is that correct?


texts: Does this normalized model for artist music records make sense?

texts:
I want to make a database about one artist's music records... CD, Vinyl, Cassette, 8 Track, Singles, Ep, Lp, etc...

I believe I have a good normalized model that will achieve my needs, however before creating the database, I want to ask the experts so that my model is solid from the start.

Here are some models that I'm looking to implement:

ALBUM (Vinyl)
-------------
**VINYL**
SIDE A
   1. Track name
   2.. Track name
   3. Track name
   ...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (Double Vinyl)
-------------
**VINYL 1**
SIDE A
   1. Track name
   2. Track name
   3. Track name
   ...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

**VINYL 2**
SIDE A
   1. Track name
   2. Track name
   3. Track name
...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (CD)
-------------
**CD**
   1. Track name
   2. Track name
   3. Track name
   ...

   Extra Tracks
   11. Track name
   12. Track name
   ...

ALBUM (Compilation)
-------------
**CD 1**
   1.Track name
   2. Track name
   3. Track name
   ...
**CD 2**
   1.Track name
   2. Track name
   3. Track name
   ...
**DVD**
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (8 Track)
-------------
**8 TRACK**
Program 1
   1. Track name
   2. Track name
   3. Track name
   ...

Program 2
   1. Track name
   2. Track name
   3. Track name
   ...

Program 3
   ...
Program 4
   ...

ALBUM (Cassette)
-------------
**CASSETTE**
SIDE A
   1. Track name
   2. Track name
   3. Track name
   ...

SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

Basically, there are albums that contain discs that contain tracks.  1 album can have multiple discs and 1 disc can only be part of one album.  1 disc can have multiple tracks and 1 track can be in multiple discs.

This is a basic normalized model I came up with. Is this a good foundation?

album (album_id, name, description, date_released, ...)

disclabel (disclabel_id, disclabel)
disc (disc_id, album_id, disclabel_id, description, position)

song (song_id, name, duration, lyrics, ...)

disclocation (disclocation_id, disclocation, position)
disctrack (disc_id, disclocation_id, song_id, tracknumber)


texts: I need a Clarification about Sql server 2014 developer edition licensing.I came to know that,for a Cheap price(50$ or something) developer edition is available.also it is given that multiple users can be connection only requirement is a developer licence for user.can anyone please explain me what is developer edition and also with one developer edition can i connect 4 client machienes to one server.?(for demonstation purposes only,so no worry about max db size limit).Kindly help me.


texts: referntial integrity issue with two tables cant delete a record

texts:
So I am trying to delete a record from customers table. I would assume it's a referential integrity issue, however I don't really see what is wrong with it. Could anyone shed some light? Please and Thanks. Error Message below

 DELETE FROM customers
        WHERE customer_id = 1

table structures:

    CREATE TABLE  payment_details  (
    paytype_id varchar(10),
   card_no  int(8) NOT NULL,
   customer_id  int(50)  NOT NULL,
   CVV  int(3) NOT NULL,
   card_type  ENUM ('Visa', 'Amex', 'American Express', 'Mastercard') NOT NULL,
   expiry_date  DATE NOT NULL,
   PRIMARY KEY(paytype_id),
   KEY customer_idfk1 (customer_id),
   CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE  customers  (
   customer_id  int(50)  NOT NULL AUTO_INCREMENT,
   fname  varchar(20) NOT NULL,
   lname  varchar(20) NOT NULL,
   dateOfBirth date NOT NULL,
   address  varchar(30) NOT NULL,
   city  varchar(20) NOT NULL,
   county  ENUM('Armagh','Carlow','Cavan','Clare','Cork','Derry','Donegal','Down','Dublin','Fermanagh','Galway','Kerry','Kildare','Kilkenny','Laois','Leitrim','Limerick','Longford','Louth','Mayo','Meath','Monaghan','Offaly','Roscommon','Sligo','Tipperary','Tyrone','Waterford','Westmeath','Wexford','Wicklow') NOT NULL,
   phone  int(10) NOT NULL,
   email  varchar(20) NOT NULL,
   payment_method  ENUM ('Visa', 'Amex', 'Cash', 'American Express', 'Mastercard') NOT NULL,
   valid_licence varchar(5) NOT NULL,
   status  varchar(10),
   PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  error  - #1451 - Cannot delete or update a parent row: a foreign key
  constraint fails (g00337857abu.payment_details, CONSTRAINT
  customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers
  (customer_id))


texts:
I would like to ask for hints / tips / pointers on the possible approaches that I might use for the following...

I maintain a database locally using postgresql 9.5, hosted on a Linux server.
However, I now find myself needing to keep a subset of this data in sync with a MS Access Jet/ACE database (specifically, a 64-bit MDE file with MDW user level security created under Access 2010). There is an air-gap between these two systems, and the flow of data is predominately one-way from postgres to access.

What options do I have, and what caveats / pitfalls do I need to beware of please? I have the ODBC driver installed, and can see all the data on the postgres server. So 'pulling' the data from postgres into access would be one approach. But my VBA is not great, and I would prefer in many ways to work under Linux with bash/perl/python/sed/awk/etc. I would also prefer, if possible, to produce a means of transferring only the changes in each table, rather than the whole contents. I'm quite happy to work with two solutions, one for each direction of data-flow. However, migrating to a MSSQL server is not an option.

Many thanks.


texts: Missing wsrep variables in mysql / percona / galera cluster

texts:
I set up a small mysql/percona/galera cluster using the percona cluster docker image, with 3 nodes.

I followed the documentation written here

After starting up the whole cluster, I'm missing some of the wsrep variables.

For instance wsrep_local_state_comment. (among many others)

Does anyone knows why is this happening ?

(I'd imagine it is not, strictly ,percona related, but I don't know)

Thank you


texts: How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

texts:
I have the following table:

InStores
+--------+---------+-----+
| ProdID | StoreID | qty |
+--------+---------+-----+
| p1     | s1      |  25 |
| p1     | s2      |  70 |
+--------+---------+-----+

and two transactions, T1 and T2.

First comes T1:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

T1 sees the sum of 95 and substracts 30 from p1, s2 (but doesn't commit yet).

T2 makes exactly the same:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

which also sees the sum of 95, as T1 hasn't commited yet and also doesn't update yet, as there is a Write Lock from T1.

Now T1 commits and T2 is now able to continue and substracts the value of 30 from p1, s2.

We end up having the value 10 for p1, s2 instead of the expected value of 40, as T2 should have seen the sum of 65 (because of the first update (substraction) from T1) and shouldn't have made that second subtraction.

I know that Oracle SQL works with Snapshot Isolation, which forces all transactions to work with the latest commited values, but how can this problem be fixed? I guess that the solution would be that when T1 is active it should prevent all other transactions from starting, but I don't exactly know how I can achieve that. What can I do?


texts: Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?

texts:
everything is in the question.

I have a table named tableA on which we have indexes and foreign keys and constraints.

I have to modify content in a column called column1. This being a production system, I want to

1) create a copy of tableA called tableA_temp
2) Perform the data change (some concatenation and regexp_replace)
3) Obtain validation that the change is correct
4) rename tableA to tableA_old
4) Rename tableA_temp to tableA.

Once renamed to tableA, would the constraints and indexes apply to the new tableA?

My guess is yes but I would need a little validation.


texts: Time taken to drop/add index is lesser than adding a column. Why is that?

texts:
I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though.

I'm performing an alter by default it does ONLINE alter.

When I add column it takes at least 50 mins for 50G table in our infrastructure, but when I performed removing and adding a composite index of 3 columns it took 8 mins.

Any idea internals of what it is doing?

Also does the duration changes if I'm adding an PK index/UK index.


texts: In MySQL Multiple instance, default instance is running, second instance is not

texts:
I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running.

root@ubuntu:/var/lib# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running

Below is my my.cnf file :

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

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

[mysqld]
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = admin123

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err
[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

When I check my error log file mysql2.err, the error was like,

130120 18:41:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-databases/mysqld2
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130120 18:41:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130120 18:41:59  InnoDB: Initializing buffer pool, size = 8.0M
130120 18:41:59  InnoDB: Completed initialization of buffer pool
130120 18:41:59  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
130120 18:41:59 mysqld_safe mysqld from pid file /var/lib/mysql-databases/mysqld2/mysql.pid ended

Below is my apparmor file.

#include <tunables/global>

/usr/sbin/mysqld {
capability dac_override,
capability sys_resource,
capability setgid,
capability setuid,
network tcp,
/etc/hosts.allow r,
/etc/hosts.deny r,
/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
/usr/lib/mysql/plugin/ r,
/usr/lib/mysql/plugin/*.so* mr,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/{,var/}run/mysqld/mysqld.pid w,
/{,var/}run/mysqld/mysqld.sock w,
/sys/devices/system/cpu/ r,
}

My second instance data directory path is : /var/lib/mysql-databases/mysqld2/mysql
Please let me know how to fix this issue.
Thanks in advance.


texts: Is command BACKUP asynchronous operation

texts:
I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location.

Is it possible that multiple backup operations are running at the same time? I have not found any "WAIT" operation in a BACKUP clause, but this option is present in SMO command (Wait) of Backup class.

Here is my partial code (full code works well, so I want to describe it as shown below):

WHILE 1 = 1
BEGIN
   SET @currentDbName = ...

   SET @sql = 'BACKUP DATABASE ' + @currentDbName + '...'

   EXEC(@sql)
   -- maybe it is a good place to wait?
   -- WAITFOR
END


texts: Is there a reason not to use white spaces for column names?

texts:
Why should I use this:

alter table `user` add column `first_name`;

Instead of this:

alter table `user` add column `first name`;


texts: tsql to find users and database level permissions mapped to a certificate

texts:
On SQL 2005 and above, how can I find users and database level permissions mapped to a certificate?

Story behing this:
I copied a database from SQL 2005 to SQL 2008. It had a certificate inside the database which got copied over with backup and restore (as far as I can see). I am not seeing any server level credentials for this certificate on both source and destination server and can see this inside the user database using the following code:

select * from sys.certificates

I couldn't find a certificate file on the server corresponding to this certificate. I doubt the following with this:

Did the certificate get copied correctly?
Did the logins get mapped automatically?
Did it loose any of the permissions on the database objects?
How can I find a file corresponding to this certificate?

I just want to make sure that If the logins were granted permissions using a certificate, then I should be able to see and retain the permissions.
The database doesn't have encrypted data.

Thanks in advance..


texts:
According to Microsoft best practices you can optimize SQL and take a lot of benefits by using this technique, my only question is how to correctly install the SQL Server to match the drives/paths according to their article:

https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

I got my system partitioned matching the article above:

C: SYSTEM
D: TEMP DATA
E: TEMP LOGS
F: SERVICES DATA
G: SERVICES LOGS
H: CONTENT DATA
I: CONTENT LOGS
J: BACKUP DATA

During the setup wizard I'm prompted for the directories (see below) but I can't figure the correct matching for all my drives/paths, I'm not sure at this point if my assumptions are remotely correct, even more, where do "Services Data" and "Services Log" will go? The options below aren't enough to match all my drives, I'm pretty sure I'm missing something and I'm lost here :)

My questions are:

How to match the drivers/directories to meet the aligned partition structure?
Is it possible to configure the Config.ini to automate this scenario?


texts:
Can we have this type of configuration?

1)MongoDB - Multiple cluster data synchronizing master to master

2)MongoDB - Multiple cluster DR

3)MongoDB - Multiple cluster Blue-green deployment


texts: When we request  CSR for SSL encryption renew , can we use same label or different label could be used ? After importing the signed certificate into Db2 server ,What steps we need to follow on db2 clients during renewing expired certificate ?


texts: Oracle OS Authentication only works on certain databases/clients

texts:
We currently face a problem where Oracle OS authentication only works on certain client/server combinations.

The environment consists of a development client with its own database server (excluded for this example), an "integration" client with its own database server (for integration tests) and a "reference" client with its own database server (we keep these identical to our customer client/server).

All servers are Windows 2003 running an Oracle 10.2.0.3 database, all clients are Windows XP running an Oracle 10.2.0.1 client. The development client is physical, everything else is virtualized on the same virtual host. All of the machines use the same network domain.

The same user (from an Active Directory) is used on each machine for tests, and both of the databases have an externally identified user created like

create user "OPS$DOMAIN\USERNAME" identified externally;
grant connect to "OPS$DOMAIN\USERNAME";

Now when trying to connect to a database using

connect /@TNS_NAME

our reference database (which still worked four months ago) declines connections with

  ORA-01017: Invalid username/password. Logon denied.

These connections work on the integration database when connecting from the development and integration client (or the integration server itself), but not from the reference client.
Note that all of the clients can connect to both of the servers using e.g. schema owner + password.

The following picture displays the current state of working and failing OS authenticated connections:

Further information:

os_authent_prefix is set to "OPS$".
os_remote_authent is set to FALSE on both servers, but as connections do work for one server, this should not be the issue.
externally identified users are written in all caps.
SQLNET.AUTHENTICATION_SERVICES is set to  (NTS) on both servers

What could be the problem here? I would suspect a configuration issue for the reference database, but then why can't the reference client connect to the integration database using OS authentication? (Note that regular connections work for that case).
Are there maybe two issues?

PS: I know OS Authentication is kind of insecure, but this is not an issue here.


texts: What does this error mean when doing a pg_restore on a Postgres 9.6.2 database?

texts:
So I have a rails 5.0.x application running on Heroku with Postgres 9.6.1. I pull down and do a pg_restore --exit-on-error --verbose --clean --no-acl --no-owner -h localhost -d database_development ~/Documents/Backups/myproduction.dump

I get the following error:

pg_restore: connecting to database for restore
pg_restore: dropping FK CONSTRAINT trial_versions fk_rails_f888baa05c
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3091; 2606 16768 FK CONSTRAINT trial_versions fk_rails_f888baa05c u7dok58iar41mh
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "fk_rails_f888baa05c" of relation "trial_versions" does not exist
    Command was: ALTER TABLE ONLY "public"."trial_versions" DROP CONSTRAINT "fk_rails_f888baa05c";

What is this error telling me? I've never had this issue before. This just started happening recently and I need to first understand the error before I can even begin to troubleshoot it.


texts: Unhandled Deadlocks with SQL Server 2017

texts:
We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before?
Detailed explanation: We migrated our database from Sybase-ASE to SQL Server 2017 (RTM-CU23) two weeks ago. In that time there have been a number of deadlocks reported in the System Health Events, three of which SQL Server was unable to kill, resulting in our business processes being blocked. We had to kill the SPID by hand, so that processing was able to continue. The queries themselves are rather complex and return a large resultset, because of this SQL-Server runs several threads in parallel (we are not limiting “maxdop”). The queries causing the deadlock were not the same every time, but they always included parallelism and tables with triggers.
During an unresolved deadlock, the “xml_deadlock_report” event, which is generated every 100ms, shows the “waittime” of most stuck threads increasing, otherwise the status and content does not change. One of the stuck threads however has a waittime which does not increase but varies between 96 and 105ms. In the “xml_deadlock_report” the process owning this thread is reported as the “victim” (shown in the picture below).The problem is that this process does not actually get killed.

This problem looks related to the recent KB Entries KB4338715 and KB4089473, but as noted above we're running the latest version. We’re looking for any ideas or hints that could help us.


texts: Migration from MySQL to Oracle generates empty tables

texts:
I'm using SQL Developer to migrate a database from MySQL 5.7 to Oracle 18c XE. The problem is that the tables are generated but empty, even though I'm specifying that the data should move online. I also looked at the scripts, and there's no data either.

What I see in the log is DataMove.DISABLE_CONSTRAINTS_FAILED. How to fix this?

These are the migration options:


texts: Recover database until cancel without prompt?

texts:
The statement:

RECOVER DATABASE UNTIL CANCEL

prompts once to select the archive log file. Is there a way to pre specify the value so as to be able to use it in a script without prompt?

I have tried:

RECOVER AUTOMATIC DATABASE UNTIL CANCEL

But it still does prompt.


texts: Oracle: Save PDB state for all instances in RAC environment

texts:
I found out the following two contradicting statements in the Oracle DBA guide.

  For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB's open mode is preserved in the following ways: ...

Then it goes on to say this :

  For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.

How do I save the PDB state in all instances in a RAC environment without connecting to all of them seperately?

Does the srvctl utlity does this automatically when I am using it to bounce the CDB as below:

srvctl stop db -d cdb_name
srvctl start db -d cdb_name


texts: Optimising a MySQL script with 1.7 billion records

texts:
I am trying to optimise a MySQL script that I have created. Here is a bit of background on the project.

We have 2 system which we are now merging into one system, I am currently writing the scripts to merge the databases together. I need this to run as fast as possible, while this script is running we have to shut down the systems and we don't want too much down time.

Both systems have a table called venue, zone and device_vendor which are identical they just have different data. When I merged the tables together obviously the IDs for the values in those tables have changed and they are foreign keys in a lot of other tables. The table I am currently working on has 1.7 billion records and all has the values venue_id, zone_id and device_vendor_id so I have to change these values to the new IDs.

Here is the script that I have written to change the IDs to the new values. All these values are also indexes which is why it is taking so long.

INSERT INTO `intelli_sense`.`tracking_daily_stats_zone_unique_device_uuids_per_hour` (day_epoch, day_of_week, hour, venue_id, zone_id, device_uuid, device_vendor_id, first_seen, last_seen, is_repeat)

SELECT day_epoch, day_of_week, hour, (CASE WHEN intelli_sense_venue.id!=0 THEN intelli_sense_venue.id ELSE 0 END), (CASE WHEN intelli_sense_zone.id!=0 THEN intelli_sense_zone.id ELSE 0 END), device_uuid, (CASE WHEN intelli_sense_device_vendor.id!=0 THEN intelli_sense_device_vendor.id ELSE 0 END), first_seen, last_seen, is_repeat
FROM geo_sense.daily_stats_zone_unique_device_uuids_per_hour AS tracking_daily_stats_zone_unique_device_uuids_per_hour
LEFT JOIN geo_sense.venue AS tracking_venue ON tracking_venue.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.venue_id
LEFT JOIN intelli_sense.venue AS intelli_sense_venue ON intelli_sense_venue.name = tracking_venue.name
LEFT JOIN geo_sense.zone AS tracking_zone ON tracking_zone.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.zone_id
LEFT JOIN intelli_sense.zone AS intelli_sense_zone ON intelli_sense_zone.name = tracking_zone.name AND intelli_sense_zone.lat = tracking_zone.lat AND intelli_sense_zone.lon = tracking_zone.lon
LEFT JOIN geo_sense.device_vendor AS tracking_device_vendor ON tracking_device_vendor.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.device_vendor_id
LEFT JOIN intelli_sense.device_vendor AS intelli_sense_device_vendor ON intelli_sense_device_vendor.name = tracking_device_vendor.name AND intelli_sense_device_vendor.description = tracking_device_vendor.description;

As you can see from the script above I have used LEFT JOINS to update these values. I have basically imported both databases into one server and now I am merging them together to create a new database called intelli_sense.

In the script above this isn't merging the 2 databases together it is just updating the values in the tracking_daily_stats_zone_unique_device_uuids_per_hour table which is from the geo_sense database because after merging the venue, zone and device_vendor tables the IDs have changed. I need to update all the tables which are related to those tables.

I have ran this script and it took 3 days to insert 50mil records which is way to long so I stopped it.

I need this script to finish in less then a day because I have 3 tables with this amount of data and I don't want too much system downtime.

I can't share the data since there is so much so I hope that script explains it enough, the actual script works its just a case of speeding it up. The data is also personal data so I wouldn't be able to share it anyway.

If you don't think this is impossible let me know and I will find a new way to do it while leaving both systems on so it can take as long as it wants.

Thanks in advance.

EDIT: Here is the explain statement for the above query.

You may notice a lot of ALLs in the type but I believe this is due to joining on the name. I have to do that because I no longer know what the IDs are since this is the reason I am using the LEFT JOINs to change them values.


texts: Detach SQL Server database without deleting it?

texts: Is there a way to detach a database without deleting it?


texts: Oracle XE/SQLPlus query display more items

texts:
Is there a setting to allow Oracle XE/SQLPlus to display more items before beginning a new header?

For example:

First Name           SURNAME              Subject                    MARK
-------------------- -------------------- -------------------- ----------
Rollo                Aslaug               Databases                    55
Rollo                Aslaug               Programming                  50
Rollo                Aslaug               Graphics                     65
Loki                 Laufeyson            Operating Systems            45
Loki                 Laufeyson            Programming                  40
Loki                 Laufeyson            Databases                    55
Ragna                Lothbrock            Databases                    80
Ragna                Lothbrock            Operating Systems            50
Ragna                Lothbrock            Programming                  65
Thor                 Odinson              Programming                  75
Thor                 Odinson              Graphics                     60

First Name           SURNAME              Subject                    MARK
-------------------- -------------------- -------------------- ----------
Thor                 Odinson              Databases                    60
Floki                Rafna                Databases                    75
Floki                Rafna                Operating Systems            45
Floki                Rafna                Graphics                     70

Is there a setting to remove the 2nd header in the middle of the query results?


texts:
I've been fooling with neo4j:

however, it appears the sandbox feature is just completely broken?

simply click here

https://auth.neo4j.com/index-sandbox-v2.html#

or perhaps https://auth.neo4j.com/index-sandbox.html#

create an act with your email (or Google/etc);

it just doesn't work - it spins forever:

Am I making some basic mistake?


texts: How to maintain referential integrity of an object graph when a node is deleted

texts:
In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained?

For example:

A product is deleted, how is the invoice containing the order which had a reference to the product kept relevant, in that the order may no longer be relevant. Moreover, how is the order kept relevant?


texts:
I have a very simple cassandra database with just one table containing the following records:

device ID       - long integer representing the device the 'value'
came from - primary key
tag ID          - long integer representing
the identity of the 'value'
timestamp       - long integer
representing the date/time the 'value' was collected
value          - decimal number or alphanumeric value, which is the data value itself

Each device transmits about 20 values every 30 seconds. we have about 500 devices which will increase to 3000 over the next 18 months or so.
once the data is stored it is never modified or even deleted (at this time).

My cassandra node sits on an AWS EC2 server with the system files on the root disk, cassandra data files on their own disk, and commit files on their own disk. all disks are currently general purpose SSD.

This data is accessed for presentation and analysis (read only) and 90% of all activity is on data that is less than 90 days old.

Question 1:
I want to be able to automatically transfer data records that are more than say 360 days old from SSD onto cheaper magnetic disk (still on EC2).
how should we set up the cassandra database server to achieve this?

Question 2:
I have an existing mysql database with over 5 years worth of very similar data which I want to migrate into the cassandra datbase.
Writing the migration tool is fairly straight forward, however, my concern is that when I execute the migration tool all the data will end up on the SSD because cassandra thinks it is all new data, rather than data that has aged in real time. Is it possible to tell cassandra, from the PHP migration tool, to put data older than 360 days onto the magnetic disk defined in question 1?


texts: Multiple handler entries in lsnrctl services output

texts:
This what I get for an lsnrctl services.

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 04-APR-2014 10:01:43

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status READY, has 2 handler(s) for this service...
    Handler(s):
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_2088_83FB68BD.ORA))
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_2088_83FB68BD.ORA))
The command completed successfully

It seems there are 2, but equal "Handler" entries in the output. What is this? Isn't it an oracle bug?


texts: How to profile individual queries using PERFORMANCE_SCHEMA

texts:
I've been looking at SHOW PROFILES, which seems ideal for what I am doing.

However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. In addition, I cannot find any information about how to use PERFORMANCE_SCHEMA to benchmark individual session queries in the same manner as SHOW PROFILES.


texts: what should be the good normalization for the following scenario?

texts:
ITS JUST FAKE TABLE DATA TO SIMPLIFY MY PROBLEM

I have tables ,,  Users , shirts, pants, belts .... each will have some data...

so by some research i have made some scenarios of links between the tables so what would be the Optimized and very good ..

Scenario 1

I will have tables

user_shirts  ( user_shirt_id (PK), user_id(FK), shirt_id(FK))
user_shirts_pants  (user_shirts_pants_id (PK), user_shirt_id (Fk),  pant_id(FK))
user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_shirts_pants_id (Fk), belt_id (Fk))

If there is

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

there will be 1 row in users table...
there will be 2 rows in users_shirts table...
there will be 6 rows in user_shirts_pants table...
there will be 12 rows in user_shirts_pants_belts  table....

then this will have data

Scenario 2

I will have tables

user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_id(FK), shirts_id (Fk),  pant_id(FK), belt_id (Fk))

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

there is 1 row in users table
-there will be 12 rows in user_shirts_pants_belts  table....

**So which scenario is good.. and in normalization form... i should adopt whi9ch scenarion... if any better scenario then this then suggest me...? So i can make more and more relational combination and i easily update and delete them... **


texts: How to define a one-to-many relationship without primary keys?

texts:
I am new to relational databases, I am not sure if I am asking this question correctly so any feedback is appreciated. I have seen a few questions about storing one-to-many relationships, but none that I have found seem to have data in quite this format.

I am more familiar with a document based approach, so I'll present my example data here as JSON and would appreciated guidance on how this would be implemented in a relational database.

For example if I have data about school classes and want to store where a student sits, in Mongo DB I would do something like the following:

[{'_id': 'asdf',
  'name': 'English 101',
  'seats': {'john': '1a',
            'bill': '1b',
            'suzie': '1c',
            'julie': '2a'},
  '_id': 'qwer',
  'name': 'Biology 205',
  'seats': {'julie': '1a',
            'bill': '1b',
            'john': '1c',
            'suzie': '2a'}
 }
]

In a relational database I am less certain. I would imagine I would have a table where the primary key is equivalent to the '_id' field a column named 'name' and one named 'seats'? However I don't know what would go in the seats field (I thought about using a JSON string, thought there must be a better way).

Does this mean that I need a new table for each unique class that contains the seat information? Or is there another way to store this data in a relational database?


texts: I have used SQL Server a fair amount but always with SSMS.  I would like to be able to use all the capabilities and understand the environment better but I do not know where I use T-sql.  I have tried a few tutorials but they seem to gloss over this simple but important part; where do I input these commands?  Is it command line?  Is it some command line type GUI that should have installed with the SQL Server installation?  In this post (https://www.tutorialgateway.org/sql-attach-database/), there is a good example of just jumping into the commands but not specifying where these commands are input.  Thank you for any help you can give me.


texts:
Sorry if this isn't the right place, this is my first time posting anything here. Anyway, I know I'm not supposed to but I can think of no other way.

I need to use a small MySQL table (70 rows x 6 columns) as a queue.

I'm writing a python application that requires a work queue to be shared between process windows (not sure what the proper name for them is).

Each job is repeatable, each use must be recorded and cleared at regular intervals (so each job is "weighted" so usage is evenly and fairly distributed). I attempted to base it on another work queue where each job is NOT repeatable, but it seems that multiple writes to the database (up to 19 at once per 12 seconds) are not properly being counted up.

Is there an alternative to doing something like this?

Perhaps some kind of cache sitting between python and MySQL that would convert many single "job + 1" updates into a singular "job + 19"?

I assumed being on a shiny new NVMe drive and a more than sufficient buffer_pool_size would make it plenty fast enough to handle that, but instead of counting up properly, over the course of 60 seconds it may reach 9, instead of 100+ where it should be.


texts: Are user defined types (UDTs) available in an Azure SQL database managed instance?

texts: Is it possible to create user defined types and user defined table types in an Azure SQL database managed instance?


texts: Good databases for unanchored LIKE condition

texts:
Basically I need a database that is good with queries like LIKE %abc%.

I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB and found out that the query like "/abc/" works really badly, and Mongo indexes support only "/^abc/".

My database structure is very simple.

Example query in PostgreSQL:

SELECT DISTINCT(id), title FROM data AS data
INNER JOIN datatosynonym AS dts ON dts.data_id = data.id
WHERE dts.synonym_simple LIKE "%abc%"

And in MongoDB

db.data.find({synonymssimple: /abc/})

Where synonymssimple is an array of strings.

Example data in PostgreSQL

data table:

id | title | timestamp
 1 |  Abc  | 1145836800
 2 |  Qwe  | 1145836800

datatosynonym table:

id | synonym_simple | data_id
 1 |       abc      |   1
 2 |       bac      |   1

My benchmarks show the following results:

PostgreSQL with b-tree indexes and %abc% query - ~15ms per query
PostgreSQL with b-tree indexes and abc% query - ~1ms per query
PostgreSQL with GIN indexes and %abc% query - ~1.5ms per query
PostgreSQL with GIN indexes and abc% query - ~1ms per query
MongoDB without indexes and /abc/ query - ~25ms per query
MongoDB with b-tree indexes and /abc/ query - ~80ms per query
MongoDB with b-tree indexes and /^abc/ query - ~0.25ms per query

Sadly I can't use /^abc/ query.


texts: SELECT statement not displaying data correctly when using variable and reusing it


texts: How to design a database for financial bond prices?

texts:
I have following flat file table composed of bond deal names and asociated prices.

Each deal name is identified by its unique CUSIP and ISIN codes and each deal name is classified with a certain deal type.

One deal name can have many prices over the course of a day.  Each price is classified with a different price type; as in the following table:

field names are shown in brackets like (XXX).

CSAMF 2A PREF (name) 12629B309 (cusip) 3,000,000 (size) M50S (original price) 55.00 (price) TALK  (price_type) CLO (deal_type)  23/09/2012 (date)
CSAMF 2X PREF (name) KYG2577W2005 (isin) 2,000,000 (size) M50S (orig_price) 55.00 (price) TALK  (price_type) CLO (deal_type) 23/09/2012 (date)
FN 465343 (name) 31381M5C5 (cusip) 2,613,100 (size) 80a (origin_price) 80a (cmbs_spread) 80.00 (cmbs_price) TALK (price_type) CMBS (deal_type) 24/09/2012 (date)
FN 465273 (name) 31381M2E4 (cusip) 1,760,000 (size) MH70s (origin_price) MH70s (Cmbs-spead) 77.00 (cmbs_money)TALK (price_type) CMBS (deal_type) 24/09/2012 (date)

Each deal name has either one CUSIP or one ISIN or both.  Prices with deal type of CMBS (Commercial Mortgage Backed Securities) are recorded from the original price into CMBS spread and prices with deal type other than CMBS are converted into money prices.

I have constructed two possible solutions for the relational database, as shown below:

Design 1:

Deals (deal_ID, name, CU_code, IS_code, deal_type)

Price (price_id, orginal_price, money, cmbs, cmbs_money)

Deal_price (deal_id, price_id, date, size, price_type)

Design 2:

 CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL
 ON [PRIMARY]

 CREATE TABLE [dbo].[price_2](
[price_id] [int] NOT NULL,
[cusip] [varchar](9) NULL,
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[size] [int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL
ON [PRIMARY]

Is it possible to construct a relational database design for this domain?

Which DDF would be appropriate for this domain?


texts: How to Virtual Populate values on GridDB

texts: I'm trying to implement virtual populate fields in GridDB (as done in MongoDB). I can't seem to find the right operator for doing so. I'm using NodeJS and Express for my backend. Would appreciate some guidance.


texts: Ways to recover a corrupted Wordpress database?

texts:
As a result of increasing PHP_Memory Limit our wordpress data base has apparently crashed and been corrupted. Our host, Rackspace, attempted crash recovery but all attempts failed.

What commands or steps are needed to get the database restored to a working state? There are no recent backups of the database, so the solution will need to be a fix of the corruption.

This is the error:

  160201 9:59:18 InnoDB: Database was not shut down normally! InnoDB:
  Starting crash recovery. ... InnoDB: Serious error! InnoDB is trying
  to free page 7288 InnoDB: though it is already marked as free in the
  tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB:
  You may need to dump your InnoDB tables and recreate the whole InnoDB:
  database! InnoDB: Please refer to InnoDB:
  http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
  InnoDB: about forcing recovery.

This is what happened before the error, here told by Rackspace in a support ticket:

  1) MySQL was not running, so I started it. I do see some indication in
  /var/log/mariadb/mariadb.log that the server has had memory
  constraints; the last time it was started (January 27), it failed to
  start with this message:

  160127 15:12:10 InnoDB: Fatal error: cannot allocate memory for the
  buffer pool

  I restarted Apache to clear plenty of free memory today, then MySQL
  started. So we probably need to look at the Apache MaxRequestWorkers
  setting. It is presently 40, but you don't have enough memory to set
  this so high. Let me go on to problem 2...

  2) Your PHP memory_limit was 256 MB, which is quite high for a
  WordPress site, but the site was still refusing to load with the
  memory error you reported. I raised the system-wide limit to 320 MB
  and then the site loaded. This is unusually high for a WordPress site,
  so you should evaluate why your site is using so much memory (it is
  likely one of your plugins), and try to reduce your usage.

  Let's say your site can function with 256 MB of memory for each
  request to WordPress. We need some memory for the OS and for MySQL,
  let's conservatively allocate 25% of memory to that. You have a 4 GB
  server. We're allocating 1 GB for the OS and MySQL, so that means you
  can only handle 12 simultaneous Apache connections (12 x 256 MB = 3
  GB). Setting MaxRequestWorkers to 40 is going to lead to a situation
  where your server runs out of memory when you get a lot of traffic. In
  that situation, the Linux out-of-memory killer will kick in and quite
  possibly kill off MySQL.

  To make your server more stable, you are going to have to do one or
  more of these items:

  Lower MaxRequestWorkers.
  Make WordPress more efficient in terms of memory usage.
  Move MySQL off-server (either on another server or use a cloud DB) so it won't contend with Apache for memory.
  Upgrade your server to increase the memory.
  Convert to php-fpm so that Apache only handles static content and then we can tune the number of PHP processes in the php-fpm
  configuration. That way Apache can use an efficient, low-memory
  process to handle requests for images, JavaScript, CSS, etc. and the
  more memory-hungry PHP requests will get handled by php-fpm processes.

  To try and make your server more stable, I have lowered
  MaxRequestWorkers to 20 for now, and also set MaxConnectionsPerChild
  to 200 so that Apache child processes won't live too long (they tend
  to consume more memory the longer they live).

Let me know if you need any more information.


texts:
I'm trying to create an account (tenant) which has full privileges on all databases called test_...
It should also be able to create other users with also only privileges on those databases.

As root:

CREATE USER 'tenant'@'%' IDENTIFIED BY '...';
GRANT ALL PRIVILEGES ON `test\_%`.* TO 'tenant'@'%' WITH GRANT OPTION;
GRANT CREATE USER ON *.* TO 'tenant'@'%';

As tenant:

CREATE DATABASE test_test;
CREATE USER 'tenant01'@'%' IDENTIFIED BY '...';
GRANT ALL PRIVILEGES ON `test\_%`.* TO 'tenant01'@'%';

That's all cool, but I don't want tenant to be able to do this

ALTER USER 'root'@'localhost' IDENTIFIED BY '...';

Can I set a special revoke for the root user? Can I do the grant create user differently?
Thank you for your help!


texts: How to categorize rows by SUM of a column in mysql?

texts:
I have a table as

id  num_items  folder
1   4
2   33
3   74
4   41
5   24
6   34
7   46
8   55
9   11

I want to assign folder number to rows but limiting the number of items in each folder (e.g. 100). Thus, increasing the folder number (next folder) if SUM(num_items) > 100.

The UPDATEd folder should look like

id  num_items  folder
1   4          1
2   33         1
3   74         2
4   41         3
5   24         3
6   34         3
7   46         4
8   55         4
9   11         5

Since, SUM make the calculation for the entire column, apparently, I need to reset the value/process of SUM after reaching the maximum item in folder.


texts: Selecting records where date range is current year minus #years

texts:
I want to select and eventually delete records from a point in time earlier than current year minus "x" years.

I'm unsure of the most efficient way to do this.


texts: What are SHOWPLAN permission security risks? (MS SQL Server)

texts:
I'm having some friction when it comes to showplan permission security.

Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).

There is one sysadmin.

There is another person (non-sysadmin) who is responsible for the data, queries, tables, views, design, structure, indices, etc on DB Number #1. And that alone.

Do you grant SHOWPLAN/ View Server State permissions on DB Number #1 to the person responsible for its design, query optimization, etc? Or does that present an unacceptable security risks to a non sys-admin?

Perhaps it's a bit subjective or a gray area, but I wasn't sure. Are there specific patches/ procedures that can be put in place to mitigate these risks?

Are there any alternatives to SHOWPLAN when optimizing queries and query run time? What about sys.sql_expression_dependencies, also a related View Server State permission, correct?

Some may argue that you have a a DB_Development, DB_Test, and DB_Production split. Perhaps ideally, but when you only have the resources to implement a Test/ Production split, maybe access on Test alone is sufficient risk mitigation?


texts: Is using the alpha version of Percona 5.6 safe?

texts:
I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it instead of standard MySQL. I've always been running Percona 5.5 with no problems. However, I'd like to update to version 5.6 in order to get a plugin that uses fulltext search working in my InnoDB (as InnoDB supports FULLTEXT in newest MySQL 5.6 versions. I have two (2) options:

1) Remove Percona and install MySQL 5.6.10 (the standard version)

2) Install Percona 5.6 alpha version, that also will allow me to use the plugin that requires fulltext in my tables that run InnoDB.

I'd like to know from anyone who knows more than me if is it safe to use this Percona 5.6 version. Nowadays I am running Percona 5.5 in my server that runs only WordPress, and I don't want to stop using Percona, but don't want something that can make me lose any data (is it possible as Percona 5.6 is new?).

Thank you all for the attention.


texts: Make Postgres database temporarily read-only (for performing volume snapshots)

texts:
The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the same time you back up the PG data. But the only way to put the application in a quiescent state is to "lock" the database also. PG lacks a database-wide or cluster-wide locking mechanism. Putting PG into a read-only state would be a piece in the following solution:

Quiesce application data (disable logins)
Quiesce database (by making it read-only)
Perform a PG checkpoint or pg_xlog_switch()
Create a snapshot of the App and Data volumes
Resume the database (make it RW again)
Resume the application
Backup the snapshots


texts: Azure SQL data warehouse - do transaction limits apply to CTAS opperations?

texts: Azure SQL Data Warehouse has limits on the maximum size of a transaction (as documented here). As CTAS operations cannot be performed inside a transaction, I was wondering if these limits still applied?


texts: Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

texts:
We are planning to support for our application another rdbms (oracle).

At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have

schema er-diagram
a full schema sql script (create table.., create index..., create trigger..., ...)
and a migrate sql script (alter table...., drop index, create index..., ...)
and finally a data file which all in it (*.mwb file)

We use table, index, stored routines, views and triggers. We want a tool, where we can design the table, relations, views, triggers and routines and export a full schema sql script for different rdbms and an migration sql skipt to upgrade a database for different rdbms.

Now my question. Is the any tool available, which is able to do the above stuff for both MySQL and Oracle?

I look at AnySQL Maestro, Navicat Premion, Altova DatabaseSpy but all loose information (mostly the triggers). I found also this wikipage, but i can't figure out, which have the feature we need.

EDIT: clarification:

Atilla Ozgur (see his answer) is right. A want a tool whit a logical model including triggers and procedure and which is able to export all the things (especially triggers and routines) to different (at the moment MySQL and Oracle) physical models. The export should be done by sql scripts.


texts: What are the main disadvantages of adding relationship between tables at later date?

texts:
During early development I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.
I tend to forget mapping the relationship between table and if I'm not wrong, this can be added later even after the table is no longer empty. This is also true for setting Index column.
I know this is probably not the best way to setup a new database for application but is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?
Thanks.


texts: How to deny access to see the logins in a database

texts: I create a login in SQL Server 2008 R2, and when that user connects to a database, it can see all the logins in the security logins.  Can I deny access to watch the logins of the database?


texts:
Is there any way to determine why is a Login is losing a User mapping?

I've created them with the following SQL ...

CREATE LOGIN [qauser] WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = [app_tst],
                           CHECK_EXPIRATION=OFF, CHECK_POLICY = OFF

CREATE USER [qauser] FOR LOGIN [qauser] WITH DEFAULT_SCHEMA = [devdba]

and then successfully used the matching login on a linked server with synonyms to tables in the original.

Some time later, the synonyms fail and checking the LOGIN properties, the User Mapping have gone for the database / user. I have to re-run the CREATE LOGIN / USER script to get them to work ... but using the main devdba login, the synonyms continues to work

What could be causing this loss ?


texts:
I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after executing the script, lot of indexes have the same results (before 50%, after 50%) and i don't know why? Can someone explain to me why indexes are not fragmented or the script is not good? Thanks

WHILE @@FETCH_STATUS = 0
BEGIN
-- > 30 rebuils
IF @avg_fragmentation_percent > 30
BEGIN
     -- > 30 rebuils
     SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +
     'ALTER INDEX ' + LTRIM(RTRIM(@IndexName)) + ' ON '+LTRIM(RTRIM(@SchemaName)) +'.' + LTRIM(RTRIM(@TableName)) + ' REBUILD PARTITION = ALL'
     PRINT @SQLString
     EXEC (@SQLString)
END
ELSE
BEGIN
 --<30 reorganize
       SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +
      'ALTER INDEX ' + LTRIM(RTRIM(@IndexName))+ ' ON '+LTRIM(RTRIM(@SchemaName)) +'.'+ LTRIM(RTRIM(@TableName)) + ' REORGANIZE  PARTITION = ALL'
       PRINT @SQLString
       EXEC (@SQLString)

       SET @SQLString = 'USE  ' +'['+ @Database_name  +']'+ Char (13 ) +
       'UPDATE STATISTICS  ' +LTRIM(RTRIM(@SchemaName)) +'.'+ Ltrim(Rtrim(@TableName)) + ' WITH FULLSCAN'
       PRINT @SQLString
       EXEC (@SQLString)
   END
FETCH NEXT FROM dbmaint INTO @TableName, @Database_name, @IndexName, @IndexID,@avg_fragmentation_percent ,@SchemaName
END


texts: How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

texts:
We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just changed to PAGEIOLATCH_EX. How can I terminate the suspended SQL Server transaction? I am not worried about losing data since all of it can be repopulated.

session_id: 54
STATUS: suspended
blocked by: 0
wait_type: PAGEIOLATCH_EX
Elapsed Time (in Sec): 19750.420000
open_transaction_count: 2


texts: Does MySQL/InnoDB use table caches when dealing with foreign tables?

texts:
TL;DR: Does a table referred to via schema.table get held in table cache?

I have a large number of client databases that our application connects to based on an http request. These are used for every part of the application except for access logging. We use a second central schema for recording of login attempts across all instances of the application.

The login table, however, is accessed via the connection to the local schema as a foreign table. For example:

 UPDATE central_schema.login SET column = 'value';

Rather than using a second connection directly to the central schema and just dealing with login as a local table:

UPDATE login SET column = 'value';

Or switching schemas:

USE central_schema;
UPDATE login SET column = 'value';
USE client_schema;

Given that during busy times I see a lot of simple update or select queries waiting for the table to close is it possible that MySQL doesn't cache tables when referred to cross-schema? Is it possible that, because it's connected to client_schema, it wants to use the client schema cache and thus the foreign table can't be properly cached?

And, if I can't get a definitive answer to that, how would I even begin to test what's in the cache and what isn't?


texts:
We have a Database and web application hosted in our office in Middle East, and we have 2 more sites in Philippines and USA.

Both offices in Philippines and USA are using the same hosted Web Application and SQL Server in Middle East but we face much latency between Philippines and Middle East due to Internet Speed.

We tried to run Merge Replication between Middle East and Philippines however it took very long time to copy the snapshot from Middle East to PHL.

Our Database Size is around 1.5 TB and we don't have any idea how we can resolve this situation we have now, putting into consideration that we need on the spot update between both Sites as users might be working on the same data at the same time

Any Clue ?


texts: mysqlslap chokes on strings that contain ";" delimiter?

texts:
I'm having a problem passing preexisting files full of SQL statements into mysqlslap.

For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with normal ; delimiting.

I also have a file called slap.sql (actually a slightly munged general-log-file, but this is a tiny example that reproduces the error) that contains

INSERT INTO comments VALUES ("I like winks ;) and frowns :(");

And I run:

mysqlslap --delimiter=";" --create create.sql --query thing.sql

I get the error:

mysqlslap: Cannot run query INSERT INTO comments VALUES ("I like winks
ERROR : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"I like winks' at line 1

Which is consistent with MySQL terminating the statement at the ; that's in the middle of a string.

What can I do to make mysqlslap take --create data from mysqldump and not hork on semicolons embedded in strings in the --query data?  Is it possible that mysqlslap does not follow normal parsing rules when you pass a file in to the --query parameter?


texts: Is there a server option which limits the amount of rows returned by a remote query?

texts:
I have an issue where a system on our network is making a query (select * from table) which might result in 0 to 70k rows being returned.

For large result sets, say around 50k, the calling system is being notified that a DB error has occurred & the query is terminated (presumably by the server). It's working with smaller result sets. The exact 'tipping point' hasn't yet been identified.

Is there a config setting in SQL Server 2012 which sets a max limit on how many rows will be returned for a query?


texts:
I have one hosting account using cPanel and phpmyadmin.

I have 50 databases under this account, all WordPress.

I need this query modified so that it runs through all databases to update the password.

UPDATE 'wp_users' SET 'user_pass' = MD5('somepassword') WHERE 'user_login' ='admin' LIMIT 1;

Hoping for a solution that can target all databases instead of having to go through them one by one.

Thank you


texts: Which is better in a master-slave setup to use local queue or write changes to remote instantly?

texts:
This is more of a a design question

I have a local db (mysql if relevant) for reads and remote master for writes but
wondering if it's better to..

1) Write changes to a local queue and bulk insert to remote master every 1 min or so

2) Write changes immediately to the remote master

Using 1 I [think] I have more control but there is the delay
and it uses 1 connection/thread per outgoing table

but 2 could be any cpu/memory usage and no control, but changes are updated in realtime.

I currently use #1 but now as my project uses more tables
I bulk insert 10+ connections and I am planning for horizontal
scalability and 10+ connections each instance could eventually add up
so please let me know what real/professional developerts/DBA's handle this
as I am just in development stage with no actual [production] experience
and just using common sense/logic currently.


texts: PostgreSQL query slows due to ORDER BY even with an index

texts:
I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes about 2 seconds on our data set.

SELECT off.provider_id, st_AsText(off.area), st_area(off.area)
FROM offices off
WHERE st_contains(off.area, :location)
ORDER BY st_area(off.area)
LIMIT 10 OFFSET :offset;

When this first happened I realized, "Duh! there's no index"; ST_area is computing the area covered by a geospatial region and since I'm ordering on it, the optimizer must be checking all containment-filtered results at query time.

CREATE INDEX offices_st_area_idx
ON public.offices
USING btree
(st_area(area));

This addition had no effect on query time. Is there a faulty assumption I'm making here? Anybody have an idea of how I can speed this query up? I'd like to render this view without having to resort to a 'loading' page.


texts: Is there any security risk in having many foreign keys referencing a single table?

texts: I have a teacher who told me that having a database with a table that's referenced by around 60% of the other tables was a security no-no. The problem is that he failed to explain why it was a security risk.


texts: Long delay before redirected restore "starts"

texts:
On several occasions, I noticed that it takes a long time before anything "happens" during a redirected restore. There are no entries in db2diag.log and list utilities show detailshows:

Completed Work             = 67129344 bytes

I assume this is because the filesystems differ between source and target machine, and that something has to be checked/prepared, but it is just my guess. Does anyone know what is happening during the delay?

Example:

db2 "values current_timestamp"

1
--------------------------
2018-02-20-11.08.39.881924

  1 record(s) selected.

db2 list utilities show detail

ID                               = 19820
Type                             = RESTORE
Database Name                    = ****
Member Number                    = 0
Description                      = db
Start Time                       = 2018-02-20 10.25.07.240025
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
      Completed Work             = 67129344 bytes
      Start Time                 = 2018-02-20 10.25.07.240029


texts: SQL Employee Clock in/out times reformatting?

texts:
I am relatively new to SQL and have written a bunch of various views.
I have been asked to created a report for our monthly payroll using clockings from SQL server.

Currently SQL formats things:

Date | Name | Clock In | Clock Out
---
01/02/2018 | Name | 2018-02-01 07:55:08.563 | 2018-02-01 10:21:42.183

01/02/2018 | Name | 2018-02-01 10:32:37.153 | 2018-02-01 13:12:33.773

01/02/2018 | Name | 2018-02-01 13:33:10.123 | 2018-02-01 15:04:16.880

However I would like it something like this:

Date | Name | Clock In | Clock Out | Clock In | Clock Out | Clock In | Clock Out
--
01/02/2018 | Name | 2018-02-01 07:55:08.563 | 2018-02-01 10:21:42.183 | 2018-02-01 10:32:37.153 | 2018-02-01 13:12:33.773 | 2018-02-01 13:33:10.123 | 2018-02-01 15:04:16.880

I have things currently working in excel using the formula:

{=INDEX(Payroll_Clockings[Start Time], SMALL(IF(1 = ((--($A9=Payroll_Clockings[Date Value])) * (--($J$4=Payroll_Clockings[Name]))), ROW(Payroll_Clockings[Start Time]) -1, ""), COLUMN() -1))}

But being an Array formula this can take a long time to refresh when there are 50+ records.

so it would be ideal to reaarange things through SQL.

the SQL view i am using is:

SELECT        TOP (100) PERCENT CONVERT(VARCHAR(19), ShopFloor.EmployeeActivity.StartEvent, 103) AS Date, Admin.Employee.FirstName + ' ' + Admin.Employee.Surname AS Name, ShopFloor.EmployeeActivity.StartEvent AS [Start Time],
                          ShopFloor.EmployeeActivity.FinishEvent AS [Finish Time], ShopFloor.EmployeeActivity.ClockNumber AS [Clock No.]
FROM            ShopFloor.EmployeeActivity LEFT OUTER JOIN
                         Admin.Employee ON ShopFloor.EmployeeActivity.ClockNumber = Admin.Employee.ClockNumber
WHERE        (ShopFloor.EmployeeActivity.StartEvent BETWEEN GETDATE() - 180 AND GETDATE()) AND (NOT (ShopFloor.EmployeeActivity.ClockNumber IN (100, 10090, 10000, 777, 999, 10001, 10098, 10002))) AND
                         (ShopFloor.EmployeeActivity.ActivityTypeCode = 1)
ORDER BY Date, Name, [Start Time]

Hopefully this makes sense and any help would be appreciated!

I don't normally create tables, only deal with creating views from current tables but I think this would be an example of our table.

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
    DROP TABLE [myTable];
END;
GO

CREATE TABLE [myTable] (
    [myTableID] INTEGER NOT NULL IDENTITY(1, 1),
    [Date] VARCHAR(255),
    [Name] VARCHAR(255) NULL,
    [StartEvent] VARCHAR(255),
    [FinishEvent] VARCHAR(255),
    [ClockNumber] VARCHAR(255) NULL,
    PRIMARY KEY ([myTableID])
);
GO

INSERT INTO myTable([Date],[Name],[StartEvent],[FinishEvent],[ClockNumber])
VALUES('03/03/2019','Abdul Pugh','2018-09-12 10:50:36','2017-09-11
05:21:44','CN-102');
INSERT INTO myTable([Date],[Name],[StartEvent],[FinishEvent],[ClockNumber])
VALUES('08/23/2017','Kiayada Knight','2018-09-18 17:22:47','2017-06-15
05:03:34','CN-101');


texts: Azure sql database how to track table schema change

texts: So someone dropped a column in a Azure sql db. How to show the table schema before the change? Restore to a previous version is not a option as that would override everything.


texts:
I have a challenge with SQL Server. We are running SQL with an inhouse developed  application. Lately we have been having a challenge where the DB hangs and users are unable to access the application.  Sometimes they have an error that says the system timed out when contacting the DB or the Application hangs. We thought it was the network, but now the network people say they have fixed the network but the problem continues. I am now faced with a problem where i end up restarting the server more than 10 times every day.

What else can i check?


texts: Postgres Identifying 2 records with 1 attribute equal and 1 attribute not equal

texts:
I am attempting to pull ids of records where attribute a is the same, but attribute b has at least 1 record that does not have a match. The db has >1million records so the statement cannot be absolute but has to change with the data.

i.e in the below I want the result to return id 1, 2 and 3 because a is equal and b is not equal in all instances, and return id 4 and 5

ID   A   B

1    y   t
2    y   m
3    y   t
4    z   r
5    z   f
6    q   c


texts: Postgres: missing disk space after truncate and re-insert

texts:
We've got a IP address range dataset stored in a table that was truncated and then re-populated with a slightly modified version of the dataset (almost identical in size).

However, after truncating the table and re-populating there is a missing approx. 1.5GB of free space even after running VACUUM ANALYZE

See graph of free space for the PostgreSQL Amazon RDS instance:

Output from VACUUM ANALYZE:

INFO:  vacuuming "global.ipranges"
INFO:  index "ipranges_pkey" now contains 9357978 row versions in 69803 pages
DETAIL:  0 index row versions were removed.
22995 index pages have been deleted, 22995 are currently reusable.
CPU 0.92s/0.18u sec elapsed 10.36 sec.
INFO:  index "ip2location_range_gist" now contains 14036924 row versions in 113046 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.34s/0.37u sec elapsed 13.81 sec.
INFO:  "ipranges": found 0 removable, 9357978 nonremovable row versions in 162161 out of 324319 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 104 unused item pointers.
0 pages are entirely empty.
CPU 11.29s/14.38u sec elapsed 290.64 sec.
INFO:  vacuuming "pg_toast.pg_toast_16398"
INFO:  index "pg_toast_16398_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16398": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "global.ipranges"
INFO:  "ipranges": scanned 30000 of 324319 pages, containing 865906 live rows and 0 dead rows; 30000 rows in sample, 13604393 estimated total rows

Some additional strange things are that the statistics for the table show both a table size and index size almost double what is expected. It also estimates 13604393 rows but a SELECT COUNT(*) returns the correct 9357978

How can we recover this 1.5GB of space?


texts: Safe way to truncate SQL Server Error Log

texts: We are running out of space. What is the safe way to clear the error log?


texts: Applying GROUP BY when using multiple CASE WHEN

texts:
select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,CASE WHEN w_id = 1 THEN fee END as value1
,CASE WHEN w_id = 2 THEN fee END as value2
,CASE WHEN w_id not in ('1','2') THEN fee END as value3
from table1

The above code gives me a result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |    50
 1   |   0      | null   | null   |   150

but I'd like to group by e_id and get result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |   200

I've tried

group by e_id

and

group by e_id, CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END

but neither of these are grouping my data as I'd like. Is there a solution to this?


texts:
Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data.

Database: PostgreSQL 9.x

Table structure (only relevant columns shown):

-------------------------------------------------------------
| id | start_at            | finish_at           | duration |
-------------------------------------------------------------
| 1  | 2015-08-14 14:01:00 | 2015-08-15 13:59:00 | 86280    |
-------------------------------------------------------------

I have the following query for grabbing the data I want but the part that is missing is the days between with zero and I'm not entirely sure on the best approach to achieve the desired outcome.

SELECT date_trunc('day', start_at) AS "day" , sum(duration) AS "duration"
FROM time_logs
JOIN users ON time_logs.user_id = users.id
JOIN account_users ON account_users.user_id = users.id
JOIN categories ON categories.id = time_logs.activity_id
WHERE account_users.account_id IN (1,2,3)
AND time_logs.start_at BETWEEN '2017-03-01 00:00:00 +1000' AND '2017-03-31 00:00:00 +1000'
AND categories.report_group = 'Segment1'
GROUP BY 1
ORDER BY 1