texts: Prioritizing values in select statement in ORACLE

texts:
Due to the JOIN for the communication method (Phone, email etc) we get alot of duplicates because a company can have multiple communication methods (CICM.CLIENT_VALUE).

My question is...
Is there a way to incorporate in the select statement to where if out of the client_Value field, if the value = EMAIL & Phone then only pull EMAIL for that CUSTOMER_ID, if they dont have have email and only phone, then pull phone

Right now I can have CUSTOMER_ID = 1 and have 2 records with the same data but the client_value will be different because they have an email, and phone, so I am getting 2 returned records instead of prioritizing 1 over the other.

Here is an image to show what I am referencing...you can see all the blacked out fields have the same value on both records but the contact_type and value are different. I would like to prioritize and choose PHONE over EMAIL if available, and not get BOTH to reduce record return count...

If this needs clarification please advise.

SELECT DISTINCT INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as CONTACT_TYPE
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS SALESPROGRESS
     , SUM(INV.SALES) AS TOTALSALES
FROM IFSINFO.HB_INVOICING_ALL INV
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD
    ON (INV.COMPANY = LD.COMPANY)
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
AND INV.STATE = '&State'
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE
       , INV.INVDATE

HAVING SUM(CASE WHEN INV.INVDATE >= TO_DATE('01-Oct-13')
                 AND INV.INVDATE < TO_DATE('30-Sep-15')
             THEN INV.SALES ELSE 0
           END) = 0


texts: Database Design: What is the best structure for storing site offices and locations?

texts:
Background

I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment resides. The RDBMS that will be used is MySQL.

The site has 5 major zones labelled Black, Blue, Gold, Green and Red. Within each zone are several offices, generally referred to as Blue 57, Gold 155 etc. Along with the offices are rooms such as cleaner's cupboards, toilets and kitchens which have no specific number. There are also a handful of larger spaces which do not technically come under a specific zone.

The Problem

My concern here is how best to store all these locations within my database.

I could of course just have a flat table with an ID and Name columns:

LocationID        Name

45                Gold 1
46                Gold 2
47                Gold 3
48                Red Kitchen
49                Blue Toilet First Floor
...

However, I recognise that there is a huge amount of repetition. Plus if the site changes zone layout, names or office numbering (has happened at least once) it would be a nightmare to fix. So my next thought was to put the major zones and spaces in their own table, so I would have:

LocationID        Name                   ZoneID

45                1                      102
46                2                      102
47                3                      102
48                Kitchen                104
49                Toilet First Floor     101

ZoneID     Zone

100        Black
101        Blue
102        Gold
103        Green
104        Red

This is definitely better, but with certain types of room - toilets, kitchens et al - there is still repetition (no two numbered offices have the same number, i.e. there is no Red 14 and Blue 14).

Is having a small amount of repetition acceptable to keep the tables simple? If I had a third table with stuff like "Kitchen", "Toilet", "Lockers" etc I would need a RoomType foreign key in the Location table. I would then have a situation where some locations have a Name and a null value for the RoomType, or the opposite and have a RoomType value and empty Name value. This doesn't strike me as being ideal.

Is there a way of doing this that avoids too much redundancy but doesn't increase the complexity massively? These tables will mostly be read with updates and inserts rare once the DB is populated.


texts: MongoDB to MySQL Replication Automatically

texts:
i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read".

so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on regular interval.

also i need to know the steps for mapping of mongo collection and mysql tables.

I cant find something similar on google so posted here.

Any help?

Thanks in advance.


texts:
I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table:

declare @val as int
SELECT @val = LongValue
from tblSystem
WHERE [Key] = @Key;

SET @val = @val + 1;

UPDATE tblSystem
set LongValue = @val
where [Key] = @Key

select @val;

Will running this within a Stored Procedure avoid any possible concurrent reads and avoid having a caller receive the same number? Should I use a transaction instead?


texts:
Hello Everyone,
This is the first time I'm doing cluster replication. But I stuck on Adding Instance. I don't know why this error is coming behind. "The error says The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer".
I am following this tutorial
https://mysqlserverteam.com/mysql-innodb-cluster-8-0-a-hands-on-tutorial/
Thanks in Advance
NOTE: The target instance '*********' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of
'********:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recovery method' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no
purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at *********:3306...
This instance reports its own address as *********
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '*********:33061'. Use the local address option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

ERROR: Unable to start Group Replication for instance 'Mysql-Cluster-Test:3306'.
The MySQL error_log contains the following messages:
  2021-02-04 12:14:06.019727 [System] [MY-013587] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
  2021-02-04 12:14:06.023702 [Warning] [MY-011735] Plugin group_replication reported: '[GCS] Peer address "*******:33061" is not valid.'
  2021-02-04 12:14:06.023739 [Error] [MY-011735] Plugin group_replication reported: '[GCS] None of the provided peer address is valid.'
  2021-02-04 12:14:06.023957 [Error] [MY-011674] Plugin group_replication reported: 'Unable to initialize the group communication engine'
  2021-02-04 12:14:06.023981 [Error] [MY-011637] Plugin group_replication reported: 'Error on group communication engine initialization'
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): *******:3306: The START GROUP_REPLICATION command failed. ```


texts:
Postgres delete doesn't actually remove the record from the table file.

Once the delete is committed and the last transaction is closed, no postgres process can see the record any more, the deleted record is now liable to be vacuumed.

Does vacuum (or some other system) overwrite the deleted data or is some of it left sitting in the table file?

Obviously vacuum full and cluster will both re-write the files omitting the deleted record, but I'm particularly interested if there are any details about what ordinary vacuum will or, will not, do to fields stored in the heap and TOAST files.


texts: SQL 2012 Replication Between Subscribers Across VPN

texts:
I have been assigned with setting up replication for an instance(remote instance) at a geo location that we access across a VPN. The remote instance will need to send the transactions to a local subscription/distributor (subscription 1) and then the transactions from that subscription will need to be pulled/pushed to an on-premise instance (subscription 2).

I am having issues figuring out the steps for setting up the configuration from subscription 1 to subscription 2. Input is greatly appreciated.


texts: Availability Group Read Only Routing Not Working for Azure App Service that connects over Hybrid Connection

texts:
We have following config of Availability Group On-Premise:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name

Recently we have setup Read Only Routing (ROR)
Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433

Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3

Issue/Question:

One of our Azure App Services (Application), is using hybrid connection to connect to on-premise AG Listener (sql-listener.domain.com)
Hybrid connection settings:

Endpoint host: sql-listener
Entpoint port: 1433

Application is using connection string:

Server=sql-listener,1433;Initial Catalog=MyDatabase;User Id=SQLAuthLogin;
Password=SQLPassword;App=MyApp;MultiSubnetFailover=True

And without "ApplicationIntent=ReadOnly" part, application CAN connect to on-prem DB just fine
When I am adding "ApplicationIntent=ReadOnly" part to connection string, app can't connect to on-prem db and throws error:

Error: A 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: TCP Provider, error: 0 - No such host is known.)

Additional Info: Application is not integrated to any VNET
Application CANNOT connect when I replace "Server=sql-listener,1433" part by "Server=192.168.xxx.xxx" (AG Listener's IP Address)

After googling similar topics, I found that when app connects to AG Listener with "ApplicationIntent=ReadOnly", listener responds and provides readable secondary database server's address to Application (I assume it is "server2.domain.com" DNS address, not IP address ?)

So I created extra hybrid connections in Azure, that have endpoint hosts and port as "server1" "server2" "server3" and 1433, and made sure these hybrid connections are added to Application and connected to hybrid connection manager at on-prem server

I hoped this way "server1" "server2" "server3" DNS names would "become visible" to Application and when AG listener responds to Application (with conn string "ApplicationIntent=ReadOnly") option and directs it to "server2" (server2.domain.com), Application would be able to connect

But Application still throws error "A network-related or instance-specific error..."

What I am missing ?
How to make Read Only Routing work with Azure App Service that connects over Hybrid Connection ?


texts: Storing more than 8000 bytes in a column

texts: I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the data which is having more than 8000 characters data?


texts:
I have a large view in Sql Server named ViewA which looks like

Select
  ColumnA,
  ColumnB,
  ...
  ColumnZ
FROM TableA
Join TableB ON
...
Join TableZ ON

This view gives me exactly what I need in some cases. However in other cases I only need one or two columns and only a few rows so I am doing something like

Select Top 5
  ColumnA,
  ColumnB
FROM ViewA
Where Condition

This top 5 query runs very slow and I suspect that SQL Server is building the full result set for all the view columns and not just for ColumnA and ColumnB

If I change the view to just return Top 5 and ColumnA and ColumnB then it is much faster.

Does SQL Server not optimize in this case if I am only selecting a few rows or columns from the larger set?


texts: slow index creation with gist and gist__int_ops

texts:
I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array.

The gin index was added since we have query of "X exists in array?" in high profile endpoint.

I created a one-time replica of the production DB and removed the index and replaced it with GIST and gist__int_ops to check it's performance (since it should work better on frequently updated /inserts table).

The gist index creation never finishes, I let it run for hours and no results on the other side, gin index creation takes few minutes.

I tried cleaning the data - I had arrays with 50 to 120 items, and I removed them and now all of my data is an array with one element and still, index creation is slow.

DB: PostgreSQL 11.5 on RDS  (db.m4.4xlarge)
Table size: 9373 MB

Any ideas what I can do here?


texts: Unable to save and error on DBCC CHECKDB

texts:
I try to save my database ≈ 260 Mb

I have this error :

  System.Data.SqlClient.SqlError : failure read function incorrect

So I try :
DBCC CHECKDB ('myDatabase') PHYSICAL_ONLY

I have this error :

  Msg 8921, Level 16, State 1, Line 1 Stop control. An error occurred
  while collecting the facts. Insufficient space in tempdb or
  inconsistency of a system table. Check out the previous errors.

I have same error without PHYSICAL_ONLY but the rest seems normal

DBCC CHECKDB ('myDatabase') WITH ESTIMATEONLY :

  9357520 Kb => 9 357,52 Mb

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

  free space in MB : 30 389 Mb

DBCC SQLPERF(LOGSPACE)

  tempbd :
  - Log size : 13 299 Mb
  - Space Used (%) : 0.01%

I do not know why this does not work

Edit : When I try xcopy (database offline) I get this error :

  target volume does not support symbolic links


texts: Finding the sum of the lowest two values from a table in MySQL

texts:
Using two tables, I am trying to show the years where the average price from Table1(Green_Certs) is greater than the sum of the lowest two prices from Table2(Trading).  The problem is I don't know how to Sum the two lowest values from the second table.  This is my code so far.  Any help is appreciated. Thanks

Select Year_Traded
From Green_Certs
Where Avg(Price)>
(Select min(Price)
     From Trading);


texts: Efficient join with data spread into different tables

texts:
I have to work with an ancient database schema on Microsoft SQL Server where records are split into different tables but referenced in a central table.

A record in my central table will look like this:

| ID | Firstname | Lastname | Table    |
| 1  | John      | Smith    | users001 |
| 2  | North     | West     | users005 |

The database will then contain multiple tables users001, users002 through usersN where individual action records are stored. I can join a users-table with the central table using the id column (ID is a fk in every users-table).
These tables can be quite long (500k rows or more).

Now, in order to do calculations over all users, I created a view that basically does a UNION ALL of all users-tables which I can then query.
Obviously that is quite expensive hence my question:
Any idea how one can efficiently join these tables?

A stored procedure is not an option unhappily as the application performing the calculations can only work with tables or views (BI Suite I cannot influence).


texts: In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow?

texts:
We are currently attempting get around a file size limitation issue with our database. The database file size hovers around the 4GB mark (the database size limit is 4GB in SQL Express 2005). We always trim data past a certain age to keep it below this limit.

To avoid this, we are looking to perform an in place upgrade to SQL Express 2014 (10GB size limitation). Will the file size limitation follow the database when utilizing the in place upgrade?


texts: Multiple servers with template MySQL databases -- how do I update/manage all of them?

texts:
I have a template EC2 instance that runs a web server as well as the MySQL db. The instance is atomic, in the sense, everything needed is inside the instance itself. Generally, I just image a blank server, and deploy. I am able to deploy multiple instances rapidly.
I didn't think my service would grow....and I am at 15 instances. However, now that I am adding improvements, I am having to add db tables, and other small changes. All that is fine, as the number is 15... but what about when it is 50 or 100? It is getting difficult to scale this, as I have to ssh in, and manually update each database in all the servers.
Are there any tools to help with this? How can I deploy/update my changes to database structures across multiple instances/servers, without losing any data? Or do I have to make a custom script/process?
I am running Ubuntu 18, with mysql 5.7 on the servers, if that helps.


texts: What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

texts:
I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real examples of issues that can pop up as a result of doing so?

Looks like the Tags are even against me on this one:


texts: SQL Server - How many type of timeouts can happen, and how?

texts:
When working with SQL Server, there can be multiple application host accessing it, and each application can have one or multiple connections. Each connection can potentially have multiple transactions (please correct me if I am wrong). Each transaction can perform query or non-query SQL.

In my experience, I easily run into timeout if I query a table that is exclusively locked. I also saw SQL Server detect and throw a deadlock exception instead of timeout if two different applications lock on the same resource. I also show rebuilding index timeout, which possible due to someone still have connection to table.

However, I also encounter a kind of deadlock where SQL Server doesn't detect it or timeout out. In this application, it opened two connections, two separate transactions, where 1st transaction locked a resource, and the 2nd transaction try to access the same resource, but it hasn't close the first transaction.

Would someone provide a list of types of timeouts and/or deadlocks, it would help me to avoid these kind of cases when working on the application.


texts: How to restrict MySQL user to not import and export database

texts: I have created a read only MySQL user but this user have an option to export the database through phpmyadmin. How can I disable the options Export and Import?


texts:
My database setup is using NDB cluster with 2-3 nodes, on CentOS. While trying to test out the performance, i tried to store ~7mil records (around 2GB size) into one of the table. at around ~200k, it failed with notification:

ERROR 1114 (HY000): The table 'my_request' is full

These are the LOGFILE GROUP and TABLESPACE:

CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.log'
    ENGINE NDBCLUSTER;

ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_2.log'
    ENGINE NDBCLUSTER;

CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_2.dat'
    ENGINE NDBCLUSTER;

Suspecting the problem lay in DATAFILE, i added 2 more into it: data_3.dat and data_4.dat, which solve the problem.

So according to this page, AUTOEXTEND_SIZE and MAX_SIZE are ignored in my current version. That table is expected to hold 700GB+ data before getting dumped into disk after pre-determined time. Therefore, Are there any way to setup the size of this DATADISK to calculate the future usage of it? Where is this data stored? (I tried to check in datadir, exec find, but found nothing). Would be really grateful if you can share some good information/strategy which explain how to work with NDB.

Note: Not an expert in NDB, please assume i know nothing when explaining. big ty.


texts: One SQL Server 2014 giving different password hash

texts:
I've been using script based off Microsoft's EXEC sp_help_revlogin script to synchronise SQL Server usernames and passwords between hosts. This has been working fine for a long time now on SQL Server 2008 R2, but with us planning our move to SQL Server 2014 I've run into a snag.
I have three staging SQL Server 2014 machines. On all the servers I run the following command:
CREATE LOGIN [TestAccount]
WITH PASSWORD = N'1234567890',
     DEFAULT_DATABASE = [master],
     CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

For two of them, I get a generated "Create" command of:
CREATE LOGIN [TestAccount]
WITH PASSWORD = 0x010075DDCA54FCEF12CB11C4E64040E877B9FF5872C41EB98095 HASHED,
     SID = 0xCA78345104805C4E89004969D05D551B,
     DEFAULT_DATABASE = [master],
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Cool. So far, so good. But, on the third server, I get a "Create" command of:
CREATE LOGIN [TestAccount]
WITH PASSWORD = 0x02002F6CB52E7F571AD422689021EB9EC1BE2AB4576AE6EC12485333A4CB892A9197B440E1471376A5AAC5160847F636A637D1F499880D7653ABC4DB4714746856E01DE41E09 HASHED,
     SID = 0x19337EA32254A64F8FC018474B58DBA5,
     DEFAULT_DATABASE = [master],
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Which doesn't work. The hash is way too big.
Is there some compatibility level flag or something that I've somehow got turned on on this one server that is generating old fashioned password hashes or something?

Giving the checkmark to Ryan as he was right. I was actually connecting to the wrong instance installed on the same physical machines as the SQL Server 2014 instances. I was very tired on Friday night.


texts:
I have two entity relations called Record_Label and Band.
I currently have:

Record_label = /\ --- band (1:M)
               \/

Where the cardinality ratio is 1:M and record_label has a total participation and band has partial participation.

I read on-line that when I have a many to partial participation, I put the table primary key from the partial participation(band) on to the many table (record_label).

But I also read that when it is 1:M then I put the record_label primary key to the band table as a fk. My question is which one should I do?


texts: Error 17807, Severity: 020 - error source identification

texts:
I have setup an alert to notify me on high severity errors. Irregularly, from time to time an error occurs:

"Error 17807, Severity: 020, Description: Event '31', which was received from the client, is not recognized by SQL Server. Contact the vendor of the client library that is being used to connect to SQL Server, and have the vendor fix the event number in the tabular data stream that is sent."

The Alert does not give me more information about it. I would like to figure out the source of this error (in principle whatever helps me to identify a user or application or query text or whatever...). I tried to setup an Extended Event session to catch this error and collect an additional data which could help me to identify the error source.

My session is defined as follows:

CREATE EVENT SESSION [ServerErrorMonitor] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([severity]=(20)))
ADD TARGET package0.event_file(SET filename=N'ServerErrorMonitor',max_file_size=(10),max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

It catch the error, but it does not collect any of defined actions and gives me only  basic data as shown on the picture below.

The other catch errors give me additional information as expected, but this one not. There is nothing more also in the error log. I also tried to google the error 17807 but I have not found anything meaningful about it...

Does anyone know some more details about this error (17807)?

Do you have any suggestion how I can identify the source of the error (user or application or query text or whatever useful...)?

The SQL Server version is 2017.


texts:
I have an archiving question.

We have a History DB used for archiving old records from our main system and the application knows to query it

However, it becomes too big.

I would like to reinitialize the History DB.
My idea was - in a nutshell:

Close down this DB,
Make it READ-ONLY,
Rename it to *old
Create a similar history DB (same structure/tables).
With the help of views/synonyms, the application will be able to query the data for both DBs without a code change.

My question is about the last part

Will that work ?

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name  ?

Our main problem is the backups, as the Archiving is done daily, we need also to back up this DB, and as it's huge DB, it's a waist of time and resources.

Thanks,
Roni.


texts: SQL Server: DB STARTUP blocking processes

texts:
I am trying to run

DBCC CHECKTABLE

(or CHECKDB, same result), but I keep getting this error:

  Check statement aborted. Database contains deferred transactions.

I've made some researches and found that it's some process with SPID 5 and command DB STARTUP blocks everything. This process is running for a few days already but neither

DBCC OPENTRAN

nor

DBCC INPUTBUFFER(5)

show anything. Looks like it just sits there and does nothing.

I've checked the logs for that database and it seems that recovery process went fine (last records are about step 3 of 3 running and that over 500K transactions were rolled back so I assume it's done).

I've tried some advice from Google but none of them helped. Setting database to SINGLE_USER, EMERGENCY and even OFFLINE changed nothing. All of them were blocked in one way or another. I can't restore it from earlier backup for some reasons, and there is no more good advice in Google.

Restarting the SQL Server makes database inaccessible saying something like "Lock timeout, error 1222" so I have to recreate virtual machine from an image. I also can't take a backup nor restore from earlier one.


texts: Passwordless mysqldump via shell script in /etc/cron.daily

texts:
I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place.

The problem:

I have a script placed in /etc/cron.daily that performs a daily database backup among the other things. It works fine as long as there is a password hardcoded into the script for the mysqldump command.

#!/bin/sh
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

However, not wanting to have the password in the script, I've set up ~/.my.cnf file (chmod 600) with my user's password stored there so the mysqldump command in the script would be passwordless.

~/.my.cnf
[mysqldump]
password="pass"

#!/bin/sh
$ mysqldump -u [uname] db_name > db_backup.sql

When I run this new script manually from the command line as root it works like a charm.

sudo sh /etc/cron.daily/daily-backup-script

But when cron wants to run it it's unable to dump the database giving the following error:

mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: NO) when trying to connect.

So, I assume cron doesn't have appropriate privilege to perform the passwordless mysqldymp command in the script, with password placed in ~/.my.cnf, however the script and the passwordless mysqldump command IN IT are working flawlessly from the command line with sudo.

Effort so far:

I've tried sudo in front of the mysqldump command in the script.
I've tried sudo -u user in front of the mysqldump command in the script.
I've chown-ed the ~/.my.cnf file as root:root.


texts: Missing "MSVCR120.dll" during the Installation of MySQL Workbench 6.3 CE

texts:
When I install MySQL Workbench 6.3 into my Windows 8.1, I encounter the following System Error :

I already tried to redownload and reinstall the program but this error dialog appears every time. How to fix this?


texts: Are there any inherently non-partitionable datasets?

texts:
Are there any practical datasets that, by their nature, cannot be partitioned in any practical way?

I am wondering about this because such datasets would be ill-suited to scaling out on commodity hardware and databases containing such data could be forced to scale up on expensive servers, at great cost.

Edit: by nature, I mean that the lack of partitionability is a consequence of the data and of the queries that must be made against it, rather than poor design choices.


texts: Why is my planning time consistently slower than execution time?

texts:
On small tables with 5,000 rows I get:

SELECT category_id FROM widgets WHERE category_id = 1;
Index Only Scan using widgets_category_id on widgets  (cost=0.28..5.20 rows=26 width=8) (actual time=0.083..0.160 rows=70 loops=1)
Index Cond: (category_id = 1)
Heap Fetches: 7
Planning time: 2.758 ms
Execution time: 0.213 ms

and on large tables with 100m rows I get:

SELECT customer_id FROM orders WHERE customer_id = 1;
Index Only Scan using orders_customer_id on orders  (cost=0.58..1372.32 rows=65243 width=8) (actual time=0.155..0.402 rows=158 loops=1)
Index Cond: (customer_id = 1)
Heap Fetches: 109
Planning time: 16.019 ms
Execution time: 0.493 ms

This seems to be a consistent theme when running a bunch of different queries on different tables.

I'm using version 10.4, here are some settings:

shared_buffers is 64GB (25% of total RAM)
default_statistics_target = 100
constraint_exclusion = partition


texts: Can I run a CLR Stored Procedure on a different server than the database instance?

texts:
I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.

My hands are tied to using this technology. Originally we were going to write a windows service that will periodically scan the table and then run the program if new records were inserted.

My question is:

Can I host the DLL on a different server so it will get run there, or when I deploy a CLR Stored Procedure dll, will it always run within the SQL Server DB instance?

The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.


texts: Improve execution time of query in PostgreSQL with multiple joins

texts:
I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions )

It can reach up to 165 seconds in run time, I was wondering if there was a way to improve this without having to add additional columns to the tables, maybe using some king of index ?

SELECT contacturl, url
FROM websites
WHERE contactable = true
  AND websites.contacted = false
  AND url IN (
    SELECT url
    FROM scans
    WHERE scanid IN (
      SELECT distinct(scanid)
      FROM misspelled
      WHERE word = ?
    )
  )
;

Edit :
As requested, the output of the explain analyze :

Edit (1) :
After Checking the explain statement output, this sing select seems to be taking most of the time :

select distinct(scanid) from misspelled where word = ?


texts: SQL Server: Failed to start debugger - Data is Null

texts:
I'm a developer working on a web application using an instance of SQL Server 2014. I use Visual Studio 2010 sp1 for development. For a task I'm currently working on, I'd like to use the SQL Server Remote Debugger to debug a very large stored procedure that we call through our ORM (Entity Framework). To do this, I open SQL Server Management Studio 2014 and type the following into a new query window:

EXEC [dbo].[MoveAllInvoiceStage]

and when I press the Debug button, I receive the following error:

  ===================================

  Failed to start debugger

  ===================================

  Data is Null. This method or property cannot be called on Null values.
  (System.Data)

  ------------------------------ Program Location:

  at System.Data.SqlTypes.SqlBinary.get_Value()    at
  Microsoft.SqlServer.Management.UI.VSIntegration.DebugSession.DebugCallbacks.OnSqlInitializeDebuggingEvent(ISqlInitializeDebuggingEvent
  sqlInitializeDebuggingEvent)    at
  Microsoft.SqlServer.Management.UI.VSIntegration.DebugSession.DebugCallbacks.Microsoft.VisualStudio.Debugger.Interop.IDebugEventCallback2.Event(IDebugEngine2
  debugEngine, IDebugProcess2 debugProcess, IDebugProgram2 debugProgram,
  IDebugThread2 debugThread, IDebugEvent2 debugEvent, Guid& riidEvent,
  UInt32 attribute)

Which is followed by a dialog that says:

  Unable to start program 'MSSQL:://<servername>/<databasename>/sys/=0'.

I am using a Windows login with a sysadmin role to connect to the database server, and all necessary firewall rules are in place on the server and my PC. I ensured that the requirements listed here are met.

The remote debugger was working when the database server hosted a SQL Server 2008 R2 instance, but it seems that it stopped working after upgrading the instance to SQL Server 2014. I can still debug on my local SQL Server 2014 instance (which hosts a copy of the remote database). Only remote debugging produces this issue.

I have confirmed that another developer on a different PC is experiencing the same issue, so the problem seems to be server-side.

Are there any additional steps I can take to troubleshoot this issue?


texts:
I got a table with multiple columns that contain DateTime namely gendate1 up to gendate8. In my stored procedure, the user will have to input a date (@date_start). Now I have to compare the inputted date to the 8 dates which will filter all the data. The problem is that I have to get the two closest dates (out of eight dates) to the given date. Sorry I'm just a beginner in sql.

In response to sir Akina

I don't have the actual code yet because it confuses me of what to do first.

Should I make a temporary table, loop the result of the query and filter each of the result (but how to get the 2 closest dates out of eight dates) then insert it to the temporary table?
or should I put all the condition under the where clause?

Example

 gendate1 = '1/6/2019'
,gendate2 = '4/6/2019'
,gendate3 = '2/6/2019'
,gendate4 = '3/6/2019'
,gendate5 = '5/6/2019'
,gendate6 = '7/6/2019'
,gendate7 = '8/6/2019'
,gendate8 = '10/6/2019'

@date_start = '1/30/2019'

How to get the gendate1 and gendate3? (they are the closest to @date_start)


texts: Mysql backup from ibdata1, ib_logfile1, ib_logfile0

texts:
I accidentally deleted my word press mysql DB. Below is the list of log files that i see in my directory where i am hoping i can get back some of my data.

I actually converted the first file (mysql-bin.000001) to sql and saw the create statements for wordpress tables. That made me hopeful about the fact that i might be able to recover all my data. However, i ran all the mysql commands in the below mysql binary log files, and could only bring up a very old version of the wordpress blog.

Can i recover my data using the ibdata1, ib_logfile1, ib_logfile0 files?


texts: What is the best way to store files metadata in DB?

texts: I have a task to create somethig like corporate data catalogue. Starting from the idea of data catalogue seems a little bit vague to me, but the question is  what is a good way to store files metadata like member_id, version etc? Usual row-wise format or JSON? I guess that json is not atomic and might be prone to  corruption, but db rows might be slightly more difficult to migrate to different instances due to the quanity of rows.


texts: How do you achieve a physically sorted table file organization?

texts:
Do I just declare an index on a column in the schema like ?

ALTER TABLE `table` ADD INDEX `index_name` (`colDate`)

I want the table records to be physically sorted not just when doing an ORDER BY query.

I believe it would be best practice to index on a date if I will be consistently query a range of dates or any query regarding a sequential range. Am I correct in believing this?


texts: How to retrieve stored procedures from raw data

texts:
I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't know how to get the stored procedures, which are what I really need out of the database.

Is there a way to retrieve them from the raw data files? (no, I did not do a dump of the stored procedures; I did not realize at the time you specify options to do that, and so I only have a dump of the tables).


texts: How do I kill Oracle DataPump expdp job stuck in DEFINING state?

texts:
My job failed because dump file already existed.
Now it's stuck in DEFINING state.

How do I stop/kill it?

declare
h1 number;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('EXPORT_TABLE_2017_1205_1402',user);
--DBMS_DATAPUMP.STOP_JOB (h1,1,0);
DBMS_DATAPUMP.stop_job (h1, 1);
DBMS_DATAPUMP.detach (h1);
END;
/

  2    3    4    5    6    7    8    9  declare
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3873
ORA-06512: at line 4

Using expdp getting segmentation fault:

 expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402

Export: Release 11.2.0.3.0 - Production on Tue Dec 5 14:56:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPORT_TABLE_2017_1205_1402
  Owner: STGDATA
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 5F9D23C27A291F20E05328C414AC3548
  Start Time: Tuesday, 05 December, 2017 14:02:38
  Mode: TABLE
  Instance: ORADB1S
  Max Parallelism: 0
  EXPORT Job Parameters:
Segmentation fault

Any idea how do kill em?

select sid, serial# from v$session where action like 'EXPORT_TABLE%';

520 1161    wait for unread message on broadcast channel
587 635     wait for unread message on broadcast channel

Trying to restart same job:

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "STGDATA.DELETE_EXPORT_TABLE", line 59
ORA-06512: at line 1


texts:
I can not get this calculation to display the answer I need. I am sure it is my lack of SQL Server skills, but this is my conundrum.

In the sample DDL Below, I am wanting to run the query (1111-666)/1111 which broken down is 450/1111 which equates to .4050

When I run each calculation step individually (AnnualSales, DeptSales, Tax) the SUM() is accurate, but when I try to run one equation to get me the ONE result that I need, I keep getting .7889 as my answer.

What in this query should be changed so that my output is .4050?

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', NULL)
,('emp1', NULL, '28.07', '11.00', '101.24', '47.03', '76.19', NULL)
,('emp1', NULL, '35.25', '7.00', '33.64', '43.54', '43.52', NULL)

Select
employee
,SUM(ISNULL(annualsales,0)) AnnualSales
,SUM(ISNULL(dep1sales,0)+ISNULL(dep2sales,0)+ISNULL(dep3sales,0)+ISNULL(dep4sales,0)+ISNULL(dep5sales,0)) DeptSales
,SUM(ISNULL(tax,0)) Tax
,SUM((
    (ISNULL(annualsales,0))
    -    (ISNULL(dep1sales,0)+ISNULL(dep2sales,0)+ISNULL(dep3sales,0)+ISNULL(dep4sales,0)    +ISNULL(dep5sales,0))
    +(CAST((ISNULL(tax,0)) AS INT)))
    /NULLIF(ISNULL(annualsales,0),0))
FROM @Calc
GROUP BY employee
ORDER BY employee asc


texts: Display non-null and null count using case statement

texts:
In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below:

Sample Data:

+------+
| Eid  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+

Output:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+


texts: Why does the CBO eliminate `distinct` and not `group by` in this query?

texts:
The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by.

My questions are:

1) Why does it choose to do so in this case - I can't see a reason from the costings and predicted cardinalities in the plans

2) If it chooses to eliminate the distinct, why not apply the same logic and eliminate the group by?

testbed:

create table t1 as
select rownum product_id, mod(rownum,3)+1 company_id
from dual
connect by rownum<=500;

create table t2 as
select t1.product_id from t1 t1 cross join t1 t12;

create table t3 as
select distinct company_id from t1;

analyze table t1 compute statistics;
analyze table t2 compute statistics;
analyze table t3 compute statistics;

fast (55ms):

select company_id
from t1
     join t2 using(product_id)
     join ( select company_id
           from (select company_id from t1 group by company_id)
                join t3 using(company_id) ) using(company_id)
group by company_id;

slow (5240ms):

select company_id
from t1
     join t2 using(product_id)
     join ( select company_id
            from (select distinct company_id from t1)
                 join t3 using(company_id) ) using(company_id)
group by company_id;

SQLFiddle here.


texts: Do I lose data if I change from INT to TINYINT

texts:
I have a database (Engine InnoDB) that was setup by someone else. I noticed that one column contains data between 1 and 107 at this moment, and it is highly unlikely to further increase much.

As it is currently set up as INT my idea was to change type to TINYINT unsigned.

Can I just do that without losing data? What would be the most efficient way to go about it?

I am still rather new to databases, getting my hands dirty at the moment and learning all about data types and performance, etc. Looks like my precursor hasn't done such a good job... :(


texts: MSSQL Old system tables: is there an hidden synonym? or multiple names?

texts:
I have tried to create new table named 'SysObjects' on dbo schema
here is an example:

CREATE TABLE sysobjects (Column1 INT );
SELECT * FROM sysobjects WHERE 1 = 0;
SELECT * FROM dbo.sysobjects WHERE 1 = 0;

Output is of sys.sysobjects view :

name        id          xtype uid    info   status      base_schema_ver replinfo    parent_obj  crdate                  ftcatid schema_ver  stats_schema_ver type userstat sysstat indexdel refdate                 version     deltrig     instrig     updtrig     seltrig     category    cache
----------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------

(0 rows affected)

name        id          xtype uid    info   status      base_schema_ver replinfo    parent_obj  crdate                  ftcatid schema_ver  stats_schema_ver type userstat sysstat indexdel refdate                 version     deltrig     instrig     updtrig     seltrig     category    cache
----------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------

(0 rows affected)

another example :

 SELECT OBJECT_ID('sysobjects'), OBJECT_ID('dbo.sysobjects'), OBJECT_ID('sys.sysobjects')

Output:

----------- ----------- -----------
-105        -105        -105

Here is an old tables mapping and this behaviour is right to all these objects

can anyone explain me how it work ?


texts: Geographic Hierarchies in a OLAP Cube / Data Warehouse

texts:
Are the following geographic hierarchies correct?
Do any US/Canadian area codes cross state/province lines?
Do any US/Canadian counties cross state/province lines?
Are there countries with counties but no principal country divisions (states/provinces)?

Zip / Postal Codes

US Style

Continent > Country > State/Province > Postal/Zip

UK Style

Continent > Country > Postal Code

Towns / Cities

US Style

Continent > Country > State/Province > City

UK Style

Continent > Country > City

Area Codes

US Style

Continent > Country > State/Province > Area Code

UK Style

Continent > Country > Area Code

Counties

US Style

Continent > Country > State/Province > County

UK Style

Continent > Country > County


texts: Does a view use a partition in PostgreSql?

texts:
I'm using a view based on two partitioned tables (they are partitioned based on same field/value) . When trying to access to a specific partition and using the explain analyse I can see postgres accesses directly to the partition I want (in both tables).

Then, created a view which is a join between both tables:

create myView as
SELECT *
FROM   tableA
       inner join tableB
               ON tableA.DATE = tableB.DATE
                  AND tableA.custid = tableB.custid

and got the view created. It's happening because tableB is an extended tableA because this one is a general one.

Now, I want to access my view using my partitioned field and retrieve data from tableA and tableB:

SELECT tableA.field1,
       tableA.field2,
       tableB.field1
FROM   myview
WHERE  tableA.DATE >= '20190110'
       AND tableB.DATE < '20190111'

I've millions of records, but when using my view it's not faster because seems postgresql isn't using my partition when retrieving data.

Can someone help me? I think when using Oracle it uses the partition, but I don't know how this works in postgresql.

All partitions are being created as:

CREATE TABLE IF not EXISTS tableA_20190101 PARTITION OF tableA FOR VALUES
FROM ('20190101')  TO ('201901012')
TABLESPACE {TABLESPACE}

All I want to do is to access faster to my view.

Thanks!

This post is about SQL Server but could this my solution?
https://stackoverflow.com/questions/34695748/create-partitioned-view-on-linked-tables
Create the view with the union all instead inner join?

Edit:
I'm using Postgresql 10.3

Edit (2) :
After reading  @jjanes post, below my explain analyse results and queries:

explain analyze
select * from tableA
    where dateA >= '20190109' AND dateA < '20190110';

Append  (cost=0.00..156664.83 rows=5037122 width=7) (actual time=0.039..2471.406 rows=4113139 loops=1)
  ->  Seq Scan on tableA_20190109  (cost=0.00..156664.83 rows=5037122 width=7) (actual time=0.038..2161.243 rows=4113139 loops=1)
        Filter: ((dateA >= '2019-01-09 00:00:00'::timestamp without time zone) AND (dateA < '2019-01-10 00:00:00'::timestamp without time zone))
Planning time: 13.815 ms
Execution time: 2642.065 ms

explain analyze
select * from tableB
where dateB >= '20190109' AND dateB < '20190110';

Append  (cost=0.00..131893.64 rows=4539809 width=6598) (actual time=0.020..1503.950 rows=3302085 loops=1)
  ->  Seq Scan on tableB_20190109  (cost=0.00..131893.64 rows=4539809 width=6598) (actual time=0.020..1215.671 rows=3302085 loops=1)
        Filter: ((dateB >= '2019-01-09 00:00:00'::timestamp without time zone) AND (dateB < '2019-01-10 00:00:00'::timestamp without time zone))
Planning time: 125.185 ms
Execution time: 1658.126 ms

Tried The following without success (I mean, could not get the explain analyse result) (supposedly my view's query):

explain analyse
select * from myView
where dateB >= '20190109' AND dateB < '20190110';

explain analyse
select * from myView
where dateB = '20190109' ;

explain analyse
SELECT
    u.custid
   FROM tableA u
     JOIN tableB t ON u.custid=t.custid AND t.date = u.date
     and dateB >= '20190109' AND dateB < '20190110';

"custid"is the PK of both tables.


texts:
I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP
First there is the bottom right index seek. The estimation says over 22 million rows but the actual data is less than 2 million rows. My guess is, the estimation is based on just the rows matching the Tag column and a look at the index's histogram seems to corroborate that. Am I right in thinking that no assumption is made about the result of matching those rows to the predicate of the join and that this is what results in the differnce between the estimation and the actual result?
Second and more importantly, the Hash Match estimates that it will return 140k rows when in actuality it is about twelve times that. This leads to the sort operator spilling into tempdb a lot, if I understand what is happening correctly. Why is the estimate so low? And is there any way to improve it?
Update: Coincidentally, the values of Record.Tag are exactly [10399, 10438]. In principle there is no guarantee that it is a continous interval, tho. I tried J.D.'s suggestion and split the query into four queries and UNION ALL'ed them together. No improvement there.
Next I tried filling a temporary table with the Record rows of those Tags:
SELECT * INTO #tmprecord
FROM Record
WHERE (Record.Tag>=10399 AND Record.Tag<=10438)

SELECT DateAndTime, r.Reading, r.Tag
FROM RecordSet
INNER JOIN #tmprecord AS r
ON RecordSet.RecordSetId = r.RecordSet
WHERE RecordSet.Collector=10090
AND RecordSet.DateAndTime >= '20201101 00:00:00'
AND RecordSet.DateAndTime <= '20210101 00:00:00'
ORDER BY r.RecordId

DROP TABLE #tmprecord

The estimation here seems better (four times instead of twelve times), but the spill takes about the same number of tempdb pages (76xx) and even more memory.


texts:
We have a stored procedure that we run regularly. It blows up the size of TempLog.ldf from nothing to 150 GB in the course of 3-4 hours. What's going on and why is it so big? TempDB.mdf is a constant 40GB. That's a fine size, and it never gets bigger. The Recovery model for TempDB is set to Simple. AutoShrink is off.

The procedure does not use any explicit transactions. It works through roughly 500 chunks of data. Some are very small, only a couple hundred rows. Some are very large, 1 - 5 Million rows. One chunk has 16 million rows.

For each chunk of data the procedure:

Reads its rows from a persistent table into a #temp table 1.
Copies those rows from #temp_table_1 into another #temp table 2,
processing them as it goes.
Creates a non-clustered index on #temp_table_2.
Does a number of updates on #temp_table_2.
Deletes rows from #temp table 2 where a specific column contains a null.
Copies rows from #temp_table_2 into a "persistent" table.
Truncates the #temp tables.

As I said, the size of TempLog.mdf consistently stays at 40GB which is fine. I need to know why TempLog.ldf gets so large and what I can do to mitigate the problem. I tried adding a manual checkpoint for TempDB at after the #temp tables have been truncated. That doesn't appear to make a difference.

Questions:

What goes on in TempLog.ldf?
Does creating an index on a #temp table take a lot of space in TempLog.ldf?
Why isn't any of this space released from TempLog.ldf when the procedure ends?
Can I inspect the contents to determine what's taking so much space?
Any other ideas about what might be going on?


texts: I would like to schedule a vacuumdb cron job for an entire database, but I don't want it to run past a certain time. Is it safe to just stop the job with killall vacuumdb?


texts: Oracle: Database writers idle until 'Checkpoint not complete' even with multiple redo logs

texts:
I work on an application with a steady data income of 400 MB/hr. For that purpose we have setup 10 redo log groups with a size 100MB each. Therefore, we roll over a log approx. every 15min.

Because of limited disk space, we have a retention job which deletes 'old' data after a certain time. When this job is run it is doing a delete cascade based on timestamps. So far so good...

When the data removal process is running the redo log interval shortens to 15s and after 9 log rollovers we see the infamous 'Checkpoint not completed' message.

We oberserved with top and iotop how the log writer (ora_lgwr_XXXX) and database writers (ora_dbwX_XXXX) behave and found that the database writers are completely idle until the point of 'Checkpoint not complete'. The logwriter writes with MB/s and the database writers idle. Regarding the documentation the database writers start on created checkpoints and these are created after every redo log rollover.

So, why are the database writers not working until the redo logs are all full?

Shouldn't I see some significant I/O for the database writers after the first checkpoint is completed with the first redo log rollover?

We see some accumalted kBs for the two datbase writers before the 'Checkpoint not completed' message with at least 9 redo log rollovers. Afterwards, the database writers store the data with a rate of MB/s to database.

Can the database writer processes made more aggressive? Idealy depending on the amount of data pending in the redo logs? I do not need to have every byte integrated into database files immeditially, but when a lot of data is pending in the database buffer cache (and the redo logs are still active) and a lot of data is coming in, there should be a mechanism to drive the database writers to a higher performance?

Metadata:

8 vCPUs
56 GB RAM
Oracle 11.2.0.3
log_checkpoint_interval=0
log_checkpoint_timeout=1800
db_writer_processes=1
10 redo log groups, 100MB each
1 1TB parition via iSCSI for all data (oradata)


texts: How can I insert records in a table that are dependent on other tables?

texts:
I have 3 tables:

table1:
Employee_class(PK),
Executive_lounge,
Executive_washroom,
Supply_closet,
Employee_lounge,
Server_room,
Server_closet

table2:
Emp_id(PK),
LName,
Years_of_service,
Emp_Class (FK) ref Employee_class in table1

table3:
Transaction_id (PK),
Emp_id (FK) ref Emp_id in table2,
Access_time,
Access_location,
Allowed

table1 is used to know if a certain class employee can access a particular location

table2 contains details of employees

table3 records every time an employee accesses a particular location. Now, access should be permitted only when the employee class of the employee is allowed to access that particular location.

So what I am trying to do is to insert into table3, but I wish to give constraints as well, i.e., I want to check Emp_Class of Emp_id from table2 and then further check from table1 if Employee_class has access to that particular location, only then allow insert in table3.

How can I do this? I was thinking about writing an insert query with a join on table3 and table2, with case but that is apparently not allowed in SQL. I am free to consider procedures and triggers but as I am new to advanced SQL, I am not sure how to do that.

Thanks. Also, I apologize if my question is framed poorly, I am new to this , please bear.


texts:
Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now.

Business Rules

The logic, for those not familiar with accounting, is: money is not created nor destroyed, it is only transferred from account to another. Each transaction has a Debit side, and a Credit side. A few examples:

Salary from your employer:
Credit Salary, Debit Bank Account - the money came from your salary, and went to your bank account.
Pay of rent:
Credit Bank Account, Debit Rent - the money came from your bank account and went to your rent account.

Accounts can be 'stock' accounts, in the sense the balance of the account is cumulative (bank accounts are a good example) or can be flux/flow accounts, in the sense that the balance of the account is non-cumulative (rent is a good example).

The logic behind the design

The idea is to have a major JournalDB table which stores the main entries. The table JournalTx stores each account involved in the transaction. Each entry (from JournalDB) has an ID, and each a transaction (from JournalTx) is linked to a Journal Entry. The base case scenario is that there's 1 entry on JournalDB and two (or more) transactions in JournalTx. Each entry can have a cost_center, a project, and a few other attributes.

There are basically two ways of designing that (as per this question) - as a one-row per transaction style, and a two row per transaction. In the first, I would have one line with the credit account and the debit account, on the second (this one) there are n-lines, one for each account affected.

Accounts

The Accounts table is the Chart of Accounts (on accountant lingo). Is has a hierarchical structure - I used the adjacency list style. Although not very frequent, accounts will have CRUD operations. I added parent_imediate, parent_second as a really ugly solution to make aggregations (calculate the total of the Assets account, for example), but given the challenge (have no idea how to make that after a long research), it seemed like an easy way out - any input or suggestion on that matter is also welcomed.

Main Queries

Get the reports, usually montlhy: basically all acounts with the aggregated transactions that affected each one of then. The best case scenario would be of a pivot table (columns as dates), with each row is an account. I guess an "stacked" version of this would work just fine also.

Accounts are just one dimension - I may want to query by cost_center or by project for example.

Other features

I want to have the ability to budget accounts (hence the budgets table), as well as have 'goals' (I want take vacations that will cost me $ 1.000).
I also want to have tags, and be able to set up recurring bills (which are "expected" transactions)

Basic relations

One entry (journal_db) have many transactions (journal_tx).
One cost_center, project, etc have many entries
One account has many transactions.
One contact has many entries.

My main doubts

I'm just starting to learn about DB/Programming, so bear with me for obvious mistakes.

Is this design solid from a programming/performance/feature point of view?
How to implement reporting? Querying the DB (derived table) or creating a new table (like journal_reports), and creating triggers to update account balances for each entry? (read in this question that is not such a great idea)
Anything that I might be missing?


texts:
It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed.

Is it still bad to use SELECT * when we want to read the entire row, or even 90% of it?
Isn't it benefitial when we read the whole row?
In normal condition, the server needs to look for each cells in SELECT col1, col2 FROM in order of the SELECT. But in SELECT *, it simply reads in order of columns? The later should be easier and faster!


texts: SQL - Spilt timestamp into multiple rows

texts:
I am dealing with data that needs to be looked at on a shift-to-shift basis (8:00:00 to 20:00:00 and its reciprocal are the two shifts) There are instances where a timestamp (one row) will span longer than a shift. Below is an example of what I am looking for.
----------------------------------------------------------------------------------------------------------
                                           Original Timestamp Data
----------------------------------------------------------------------------------------------------------
   START_TIME             END_TIME
2020-07-16 04:54:50  2020-07-27 06:36:14

----------------------------------------------------------------------------------------------------------
                                           Updated Timestamp Data
---------------------------------------------------------------------------------------------------------
-
   START_TIME             END_TIME
2020-07-16 04:54:50  2020-07-16 08:00:00
2020-07-16 08:00:00  2020-07-16 20:00:00
2020-07-16 20:00:00  2020-07-17 08:00:00
2020-07-17 08:00:00  2020-07-17 20:00:00
        .                      .
        .                      .
        .                      .
2020-07-26 20:00:00  2020-07-27 06:36:14

Here is the code I have tried but I am only able to split the data into two rows. SOmething tells me that the "Start Roll" and "End Roll" Columns within #T1 are not going to work in a situation like this.
Declare @DayTurn as DATETIME, @NightTurn As DATETIME, @TodaysDate As DATETIME, @DateCheck As DATETIME, @TimeChange As Integer, @MidNight As DATETIME

Set @DayTurn = '8:00:00'
Set @NightTurn = '20:00:00'
SET @TodaysDate = GETDATE()
SET @DateCheck = CASE WHEN DATEPART( WK, @TodaysDate) >= 7 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
    ELSE DATEADD(Week,-6,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
END;

SELECT
      (Case
        When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC]) as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), DATEADD(hour, @TimeChange, Activity.[END_TIME_UTC]) , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
        else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'START_TIME'
      ,(Case
        When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
        else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'Start Roll'
      ,(case
        When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC], 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
        else CONVERT(datetime, Activity.[END_TIME_UTC]) end ) As 'END_TIME'
      ,(Case
        When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC] as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[END_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
        else CONVERT(datetime, Activity.[END_TIME_UTC]) end) as 'End Roll'

      into #T1

  FROM [MY_DATA] as Activity
  Where Activity.[START_TIME_UTC] >= @DateCheck

SELECT * INTO #T2 from(
Select
    temp.[START_TIME]
    ,temp.[END_TIME]
From #T1 as temp
UNION
Select
    temp.[Start Roll]
    ,temp.[End Roll]
From #T1 as temp
) as temp;

SELECT
  *
FROM #T2
Order By START_TIME;

Drop Table #T1
Drop Table #T2

Any and all help is greatly appreciated.
Cheers!


texts: How to import .dbf files (with more than 256 columns) to SQL Server?

texts:
I would need once a month to import 12 .dbf files to SQL Server 2017. Files have dBase III version. They have a similar structure. They have no PK and no unique index and no combination of columns can be treated as unique index. Each file is around 1GB size and has 100K-300K records. Most troublesome thing is that all of them have more than 256 columns.

That said all MS ODBC/OLEDB drivers fail to read them properly.

The only thing I have found working so far is Devart (paid) ODBC driver.

Are there any other solutions?


texts: PostgreSQL pg_restore: How do I save script report to a txt file?

texts:
I need to run pg_restore on my PC with Windows CMD to restore a dump to a remote DB. I need to save the running report of the pg_restore into a file, to make sure the restoration ran as expected. Below is the pg_restore command I ran.
pg_restore.exe --host=10.10.10.2 --port=5432 --username=postgres
               --dbname=mytargetdb --verbose --single-transaction "AT5.DUMP"

It turned out that:

Putting > report.txt in the end of the pg_restore command does not render error, but nothing got written to the textfile.
Using the --file=report.txt throws "error: options -d/--dbname and -f/--file cannot be used together"

The restoration was actually successful. But copy-paste from the cmd windows is not an option as the resulting report was long enough so the cmd only shows some hundreds of the last line of the report, and not the most from earlier lines.
So how should I do that?
My environment:

My PC: pg_restore version 12.2 on Windows 10 Pro
Target server: PostgreSQL 9.6 on Windows Server 2008 R2 Data Center


texts: Oracle - User to not see data from other users

texts:
I've done some research about it but I found some answers that are not clearly saying if it is possible or not.

Right now, I have a user admin. Everyone in my team is using it to create other users. One user = one project.
When we create the users, we grant all privileges to him.

Now, we would like to be a bit more secure (I know, it's absolutely not secured right now).

I thought, we could have a user, let's name him "User_Creator", to have the sole purpose of creating other users (projects).
The new user, let's name him "Project1", can do whatever he wants in his own schema (create/drop/alter/insert/delete).
When User_Creator, creates another project (Project2), I would like that Project1 can not see data from Project2 and Project2 from Project1.

Is that even possible ? I understand that the grant/revoke is done on the object-level right ? Do I need to create a trigger to revoke access to other users immediately after the creation of a new object?

Edit1: I've created my user "User_Creator" like this:

CREATE USER user_creation IDENTIFIED BY abcdef;
GRANT RESOURCE TO user_creation WITH ADMIN OPTION;
GRANT dba TO user_creation with admin option;
CREATE ROLE user_creation_role;
GRANT CREATE SESSION TO user_creation_role with admin option;
GRANT ALTER USER TO user_creation_role;
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT DROP USER TO user_creation_role;
GRANT CREATE TABLE TO user_creation_role with admin option;
GRANT user_creation_role TO user_creation ;

And a project is created like this:

CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1;
GRANT dba TO project1;

Edit2:

The answer is to not grant DBA and only grant the creation.
That way, user will not share data.
Thank you for your help guys.
Sorry if it was a bit of a rookie question.

I create users like this now:

CREATE USER user_creator IDENTIFIED BY abcdef;
GRANT RESOURCE TO user_creator WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO user_creator with admin option;
CREATE ROLE user_creation_role;
GRANT CREATE SESSION TO user_creation_role with admin option;
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE TABLE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE VIEW TO user_creation_role WITH ADMIN OPTION;
CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1 ;
GRANT CREATE TABLE TO project1 ;
GRANT UNLIMITED TABLESPACE TO project1 ;
GRANT CREATE VIEW TO project1 ;
GRANT CREATE PROCEDURE TO project1 ;


texts: Large table with geospatial data - slow queries during updates

texts:
I have a large table with millions of rows (geospatial data) which is getting updated several times per day.

Selects by lat/lon are very fast (0.02-0.04 seconds) most of the time, but during updates the same queries can take as long as 5 seconds. Profiling shows them hanging either in the "sending data" or "statistics" state. Of course there is a lot of I/O going on during these mass updates which are also quite time-consuming because every row needs to be updated.

The storage engine used is InnoDB, though I had to keep buffer pool size at 128M (a larger buffer pool appears to put more strain on the server during update/cleanup operations which even results in failed/stalled connections).

Any suggestions for tuning? I wondered if maybe there is a way to specifically limit I/O write operations or prioritize Selects over Updates/Inserts (with the latter ones not being that time-critical).

Table structure:

+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| vt                | datetime    | NO   | PRI | NULL    |       |
| lat               | double      | NO   | PRI | NULL    |       |
| lon               | double      | NO   | PRI | NULL    |       |
[lots of data fields]

my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=100
thread_cache_size=1
long_query_time = 1

open_files_limit=8192
table_cache=3000
table_open_cache=4000
table_definition_cache = 3000
query_cache_limit=256k
query_cache_size=100M
query_cache_type=1
query_cache_min_res_unit = 2k
thread_cache_size=5
read_buffer_size=1M
sort_buffer_size=2M
join_buffer_size=2M
max_heap_table_size=4G
key_buffer_size=3G
tmp_table_size=4G
innodb_file_per_table=1


texts:
When working on a game, I developed a database that used X/Y coordinates, roughly as:

CREATE TABLE Map
(
    MapId INT IDENTITY PRIMARY KEY,
    X INT NOT NULL,
    Y INT NOT NULL,
    North INT FOREIGN KEY REFERENCES Map(MapId),
    South INT FOREIGN KEY REFERENCES Map(MapId),
    East INT FOREIGN KEY REFERENCES Map(MapId),
    West INT FOREIGN KEY REFERENCES Map(MapId)
);

(I'm not at my editor, so that may have syntax errors.)

I then implemented the path searching algorithm (A*, specifically) inside a stored procedure.

Is there a better way to implement this coordinates system?
I had implemented it in SQL Server, since that is what I had on hand and what I was familiar with.  Is there anything in another database system that would've simplified this problem?

It may make a difference to know that this, being a game, is strictly a grid system. That may simplify (or complicate) the problem.

Edit:

Some sample data:

map_id      x           y           north       east        south       west
----------- ----------- ----------- ----------- ----------- ----------- -----------
20          502         501         26          33          21          18
21          502         502         20          22          87          19
22          503         502         33          23          88          21


texts:
I created a procedure to rebuild indexes with a cursor. Instead of executing properly, I get an error message:

  Msg 2812, Level 16, State 62
  Could not find stored procedure

What to do to get rid of this situation?

USE [ACT]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
ALTER PROCEDURE [dbo].[myProcedure]
AS
BEGIN

DECLARE @idx_name VARCHAR(200) -- Index name
DECLARE @tableName VARCHAR(256) -- Table Name
DECLARE @sql varchar(500)

DECLARE Index_Rebuild_Cursor CURSOR FOR
SELECT OBJECT_NAME(ps.OBJECT_ID)tbl_name,b.name idx_name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.avg_fragmentation_in_percent>80

OPEN Index_Rebuild_Cursor
FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name

WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql='ALTER INDEX '+@idx_name+' ON '+@tableName+' REBUILD'
 EXEC @sql

 FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name
END

CLOSE Index_Rebuild_Cursor
DEALLOCATE Index_Rebuild_Cursor

END


texts:
I have a SQL Server 2008 R2 used for sharepoint and ssrs. The SQL Server hosts two instances.

For a few minutes both instances were timing out, not responding.

CPU slowly rose to 100%, 30% from both instances, and 30% from the OS (and a few smaller things).

Afterwards, 16GB of memory was unallocated from SQL Server, a few GB of memory was used by some process (can't tell which on the esxi).

Since then, SQL Server again has its maximum amount of memory, and PLE is around 15000.

What I'd like to know is, how can I track WHY SQL Server was forced to give up its memory?

Either a history somewhere in the trace file, or a windows event? (I struck out on both so far)

I tried:

DECLARE @log NVARCHAR(100)
SELECT @log = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT
    g.DatabaseID,
    g.LoginName,
    g.StartTime,
    g.EndTime,
    g.DatabaseName,
    g.FileName,
    e.name
FROM  ::fn_trace_gettable(@log, 0) as g
inner join sys.trace_events e on g.eventclass = e.trace_event_id
inner join sys.trace_categories as c on e.category_id = c.category_id
inner join sys.master_files as m on g.databaseID = m.database_id and g.filename = m.name
order by StartTime desc

But couldn't find anything during the time of the outage.

If it turns out not to be possible to find out why this happened, how can I monitor this in the future.

EDIT:

SQL Server version: 10.50.6000.34 Enterprise edition,

The server is being virtualised in esxi 5.5,

Page file is set at 8GB.

The error log shows a single failed logon of a sharepoint service user. (And a lot of successful log backups)

Update:

I've since had confirmation from the VMWare team that no Vmotions happened during this period on any related machines. And no ballooning occurred that might have affected the machine.

I've also had the network team confirm that Nagios showed no noticeable spike in latency on any of the machines related to this farm.

And we've since excluded any possible reports that were running at the time.

We have however seen 3 other occurrences of a 16GB of memory drop from SQL server in past statistics. 2 of which were related with reports of a temporary decrease in performance.


texts: Mirroring partner timeout - Not working the way advertised?

texts:
I'm testing a 2 node windows failover cluster with mirroring in high safety mode and automatic failover. My mirror is a standalone server as is the witness. All instances are 2008 R2 RTM Enterprise Edition (64-bit) (this is a test, the witness will be Express in prod)

Because I'd like to prevent failover to the mirror when I failover one cluster node to the other (or in the case we just lose a node), I've upped the partner timeout value. My cluster fails over fairly quickly, somewhere between 25 and 30 seconds. But, even when I set the value of the timeout to 59 seconds, the mirrored DB still fails over to the mirror server, and rather quickly.

Generally, I'm testing by simply moving the SQL resource from one node to the other in the cluster manager but I did try it by rebooting the active node as well. The same is true when I failback. I can shutdown the SQL Service on my mirror server (which is now the principle) and by querying sys.database_mirroring, I see the mirror (former principal) change state and description in very short order.

So, what am I doing wrong or assuming about how the timeout works?


texts: How to backup a large (100gb+) microsoft sql database for occasional "offline" use?

texts: I have a Microsoft SQL database that's over 100gb currently in service. It's due to be decommissioned but I want to keep the data for occasional use without paying for a Microsoft SQL licence (currently provided as part of a monthly subscription by my server provider). Unfortunately Microsoft SQL express has a size limit that is much lower than my database size. What options do I have for retaining this data for only occasional use by a single user?


texts: Performace of SELECT query based on primary key vs. indexed column

texts:
I have a scenario where a row in a table can be obtained by using either primary key or another column which is indexed:
SELECT * FROM table_name WHERE primary_key = ?

vs.
SELECT * FROM table_name WHERE another_column = ?

another_column has unique values and has index created on it.
I am using Postgres, and on doing explain analyze on both SELECTs, they resulted in almost same execution time.
So, my question is, generally, is the performance for both the queries same or is one query better than the other? And why?


texts:
I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute?

From my limited understanding I think it's a table level lock that isn't releasing. The table pg_locks shows a single AccessExclusiveLock that has its granted column set to f. Running ps aux | grep postgres confirms that there is a postgres ALTER TABLE waiting.

If it helps the statement is one that's been created by the Django ORM (South in particular).

Thanks.


texts: How to connect to remote MySQL server with sshuser and mysqluser being distinct

texts:
I'm trying to connect to a remote MySQL server via ssh so that I have write-access to export some data. At the moment I can do the following:

Connect to the remote MySQL using mysql -u mysqluser -pPASSWORD -h HOSTIP DATABASENAME
Connect to the machine using ssh sshuser@HOSTIP

(Note that HOSTIP is the same for ssh and mysql server, but mysqluser and sshuser are different).

However, I cannot connect to remote MySQL via ssh. I tried two things:

After ssh-ing into the machine, I tried mysql in the terminal but it is not installed.
I tried ssh -L 1234:mysqlhost:3306 sshuser@sshhost. However, I can only enter my sshpassword, after which I get ssh-ed in but no connection to MySQL still.


texts: MySQLWorkbench with MariaDB 10.0 Performance tab not working

texts:
I'm using MySQLWorkbench 6.3.3.0 (build 592) and I cannot use the performance tab on a Mariadb10 connection.

This is the error I receive:

  This feature requires MySQL version 5.6.6 or newer

The error as you can see is that workbench cannot see that MariaDB 10 has the performance schema and is assuming that the MySQL version is lower than 5.6.6 which somewhat is true as I'm not using MySQL.

I can assume that this is a compatibility issue between MySQLWorkbench and MariaDB 10?

AFAIK MariaDB is supposed to be compatible with MySQL 5.6 or am I wrong about that?

Has anyone a workaround for this?


texts: What is better? Many articles in one publication or many publications with one article each one?

texts: I have a large database with several transactional replication publications.  Each publication has 15-20 articles.  Some subscriptions are push, so each time a new article is added to the publication, a new large snapshot must be created.  In order to avoid this, I'm thinking to create a single publication per article (not adding the arcticle to an existing one), thus reducing the need for a large snapshot.  I'm wondering if this approach has any downsize.


texts:
Good day, I know very little about this topic and need some advice on where to start.
I have two Microsoft SQL production databases that I would like to replicate so that I can run my reporting on them instead of the production databases.

Question 1: Does it make sense to use replication for the above scenario?
Question 2: What do I need in terms of Microsoft licenses for replication?

Thanks for reading.


texts:
Part of our software solution is responsible for processing files that get uploaded to us by our clients. We want to process them as quickly as we can.

Our system is architected so that there's a single redis job queue, and the processors live on separate processing servers. We can turn a processing server on/off individually. Ideally, the more processing servers we have turned on, the faster we are able to process uploaded files.

The entries in the redis job queue point to rows in MySQL. In order to actually do the processing on the uploaded files, we need to read from and write to MySQL.

The two graphs below show how our system performance changes as we add more processing servers. The green lines on both graphs show the number of processors running. Notice that as we add new processors, the rate at which we process files increases for a while, levels off and then decreases. The average select query time steadily grows, and then explodes. (Ignore the spike in select time around 11:04.)

I feel pretty confident that MySQL is reaching a performance maximum. However, I am not a MySQL admin, nor do I play one on TV.

Do you all have any suggestions about how to track down this performance issue? I can tell you that there are no queries in the slow log; that the machine has 24 cores, and each time I add a new processing server to the mix, the CPU % on mysql goes up by about 100%.


texts: How can I free space in oracle mount point (/u01/app/oracle/)?

texts:
My question is what can I delete or how can I free space so that I can log onto oracle via sqlplus as sysdba. When I execute the command df -k to see the free space in the current directory it appears to be full and on the other machine its at 52%.

Filesystem                   1K-blocks     Used        Available  Use%    Mounted on

/dev/mapper/rootvq-rootlv    51606140    48985496      0          100%    /


texts: what is the best practice for tempdb autogrowth?

texts:
I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app.
his temp db files are one mdf one ndf and of course an ldf.
the mdf is on the same drive as the other mdfs and so if the ldf file.

When I set the autogrowth properties I know I should keep it as unrestricted growth but when I need to choose between autogrowth by % or MB what to choose? and how do I know what number to configure?

Thanks in advance!


texts: how to deal with mysql 500 million data per month?

texts:
this is a interview question

a telecom billing system, there is a internet flow table (phone_number,start_time,stop_time,time_lasting,fare),500 million records per month(2G),every record size is 300K, insert and update when people use 4G to surf the internet,and update their account balance every time（166 write/s），and people will query their internet details from 5 * 6 = 30 hundred million (save half year data)

question1:how to use mysql to handle these data?
quesiton2:how to optimize insert and update?
question3:how to optimize query ?

my opion (mybe not correct)
1.insert and update is a transaction so use InnoDB engine
2.partition table every day
3.do I need redis or anything to help speed up insert and update ?
4.I don't know how to deal with this query optimize

thank you very much


texts: mysql off-site replication ( file only )

texts:
I am new to mysql, I have extensive experience with MS Access and some with Tivoli. I have tried to search the net for my solution but there is such a massive quantity of results I can't find a solution. My question is a two part.

What I need to do is to have mysql operate two database files, the first would be a normal setup, the second would not. I need the second file to be on a remote file system, possibly on my google drive. I would somehow need to create the db locally then xfer it to the gdrive, then have mysql connect to the shared url.
Part two: mysql will need to have full two replication between the two db files. I want to be able to delete records on the gdrive file. I know how to do this in Access but I have no idea how to do this in mysql. The server is behind several NAT's and can only make outbound connections, I cannot change this. I will be using Java for input/reading/maintenance. On the server side Java will work normally. On the gdrive side, will the server be able to handle java making deletions directly?

Can this be done? Any thoughts and comments are welcome.


texts: Determine sql server installation (server or client tools only)

texts:
Think about the situation that you have a software audit.
The company use a software facility management tool to determine installed software on a machine.
This software that seek for installed software is able to create packages to find software.

At the moment the software seeks for special registry keys to see if sql server is installed.

I got a false alert on one machine, because there only management studio (client tools) are installed.
So I need to identify if the full server database server is installed or client tools only. Can I determine this finding registry keys?


texts: How much of a view is persisted when you create an index?

texts:
Say I have a view defined by the following SQL:

SELECT t1.id, t2.name, t3.address
FROM Table1 as t1
INNER JOIN Table2 t2
  ON t1.id = t2.tID
INNER JOIN Table3 t3
  ON t1.id = t3.tID

From what I understand, if I create an index on this view then the data will become persisted, but I'm unclear what exactly gets persisted.  Say I create a unique clustered index on t1.id, will all three columns be saved to disk or will the values being pulled from table2 and table3 still be calculated at runtime?

Let me know if any of this doesn't make sense or if I have left anything important out.


texts: Set PK as a combined string of table 1 and 2 records

texts:
Say, for example, I have 2 tables - Table 1 is Drink Type and Table 2 is Flavor. What is the query to create the unique identifier column which would result to drink_type_drink_flavor?

+-------------+-------------------+
| drink_type  | drink_flavor      |
+-------------+-------------------+
| Fruity      | Apple             |
| Coffee      | Caramel           |

Expected PK for above would be Fruity_Apple and Coffee_Caramel


texts: Replicating a View to a Table. Issues with schema binding, view indexing

texts:
I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server.  Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS.  I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end.  I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property.  When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

So I go to index the view, but I can't because it's not schemabound.

So then I go to schema bind the view but I can't because the view is attached to a remote database.

Cannot schema bind view 'dbo.MyView' because name 'RemoteDb.dbo.MyTable' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

And it appears it is impossible to set schema binding on a view that queries a remote database.  What the heck are my options here?

Thanks


texts:
Converted model resulted into incomplete script generation as all stored procedures, triggers and views were missing, only table structures were generated and i did not get any error messages for it.
I followed below documentation

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm.

at this step : Generating and Executing the Script to Create the Oracle Database Objects


texts: What is the safest way to switch the binlog format at runtime?

texts:
Because of the following warning in mysqld.log:

  [Warning] Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
  because it uses a LIMIT clause. This is unsafe because the set of rows
  included cannot be predicted.

I want to switch the replication format to MIXED.

But according to the MySQL document:

  Switching the replication format at runtime is not recommended when
  any temporary tables exist, because temporary tables are logged only
  when using statement-based replication, whereas with row-based
  replication they are not logged.

So, the question is how can I identify if there is any temporary tables exist to switch the binary log format safely?


texts: Delete millions of rows based on ids in another table

texts:
I have two tables on MySQL:

HISTORY_TABLE (4 million rows) (column1 indexed)
EXTRACT_TABLE (600K rows) (column1 indexed)

For every EXTRACT_TABLE.column1 there are multiple HISTORY_TABLE.column1 rows.

Objective: I want to delete all records that MATCH criteria:

HISTORY_TABLE.column1 = EXTRACT_TABLE.column1

I tried two different possibilities:

Simple query that matches criteria (10 hours)
Copying the NOT EXISTS rows into a new table (2days+)

I found via searching that deletion can be done in chunks with a stored procedure, but I don't know how.

Is there an example on how to do a cursor based on EXTRACT_TABLE to read in chunks and delete the other table? Or is there other way to accomplish my objective?


texts: log shipping backup fails every so often on only one server

texts:
I was asked to change the DFS share for our log shipping on all our SQL servers (SQL 2008 on windows 2008) to point to the new share due to some IO issues on the previous one. But now, ocassionally, on one of the important production server, I get the following error every so often. Please note that I do not get this for every transacton file and also that the error seems to be occuring only from one of the server. Once the log shipping fails with this error, our DR logshipping is broken (ofcourse) But all the consecutive log shipping backups for this server just continue working fine.

Message
2012-06-18 09:40:35.37  * Error: Backup failed for Server 'XXXX'. (Microsoft.SqlServer.SmoExtended)
2012-06-18 09:40:35.37   Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
2012-06-18 09:40:35.37   Error: Write on "\\...FILE23.trn" failed: 2(The system cannot find the file specified.)
BACKUP LOG is terminating abnormally.
100 percent processed.(.Net SqlClient Data Provider) *
2012-06-18 09:40:35.39  ----- END OF TRANSACTION LOG BACKUP   -----

My questions:

Why would the backup for a transaction fail every few hours only? The consecutive backups just run fine.
What are the factors I need to check for this type of issue?
Why would I get an error as "cannot find the file" when it is supposed to be doing a write?

Any help on this is deeply appreciated.

Thanks in advance


texts: Database Design for a Graph (Network Topology)

texts:
Is there a recommended pattern for modeling a graph in a relational database?

My presumption is that you'd have the following three tables:

Graphs
------
GraphID, PK
GraphName

Each record in the Graphs table represents a single graph you are modeling.

Nodes
-----
NodeID, PK
GraphID, FK
NodeName, string
...

Each record in the Nodes table represents a node in GraphID, specifying attributes specific to that node.

Edges
-----
EdgeID, PK
GraphID, FK
FromNodeID, FK
ToNodeID, FK
EdgeName, string
...

Each record in the Edges table represents an edge in GraphID, specifying the Nodes it connects and the attributes of an edge (name, distance, etc.).

What I'm having a hard time wrapping my head around is what if there are different types of nodes, where each type of a node has some attributes in common (say 5 or so), but has many unique attributes (say 10-30)?

For simplicity's sake, imagine there are two types of nodes to capture: A and B. How do I model this? I've seen one option being to have a "general" Nodes table:

Nodes
-----
NodeID, PK
GraphID, FK
NodeName, string
AnotherSharedAttribute, string
...

And then a detailed table for node type A and node type B:

NodesA
------
NodeID, PK
UniqueToA1, string
UniqueToA2, string
...

NodesB
------
NodeID, PK
UniqueToB1, string
UniqueToB2, string
...

Am I understanding this approach correctly? Because what flummoxes me is how do I query this efficiently? It seems to me that to determine the attributes for a particular node, I have to check all possible node type tables (NodesA and NodesB in this case) for a matching NodeID. This might not be a big deal with just two different types of nodes, but in my project there are ~30 different types of nodes and 5 different types of edges.

Thanks


texts:
SQL2012.

I have a user and only want them to have select access to a single view and nothing else.

I tried to create my own schema containing the view and deny access to dbo but it seems it doesn't work as I would have expected it to. I can still run queries against tables even though I can't seem them in SSMS.

CREATE SCHEMA vl
CREATE VIEW vl.vw_Search AS SELECT 'This is a test' AS RESULT
GRANT select ON vl.vw_Search TO vlu
DENY SELECT On SCHEMA::sys To vlu
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To vlu
DENY INSERT, UPDATE, DELETE ON schema::dbo to vlu

DENY VIEW ANY DATABASE TO vlu
DENY VIEW ANY DEFINITION TO vlu

I can also still access system views which I also don't want the user to be able to do.


texts: PostgreSQL decrease performance after migration

texts:
After postgresql database server v9.3 migration to v9.6 I noticed a decreace in the performance of the entire system. The config parameters are the same as in v9.3 taking in to account the next parameters:

shared_buffers = 10000MB
work_men= 64MB
maintenance_work_men = 1024MB

Also I tried to monitor some resources an this is the result

              total        used        free      shared  buff/cache   available

Mem:            31G        385M        4.5G         10G         26G         19G

Swap:          3.0G          0B        3.0G

Also when I run some queries the server internally launches queries like these ones:

select typname from pg_type where oid=1043
set search path to public
deallocate pdo_stmt_0000000e

And then runs my query but I'm afraid that here is some impact in performance after migration. I have another 9.6 server with a fresh install no migration and it's not presenting that problem (response time). It seems to be expending to much time in those queries.

Do you have any tip or advice on how to fix this?

Thanks in advance!


texts:
I have following problem regarding the pg-pool (3.3.3)

When master fails, automatic failover happens and standby is promoted to master. But this new master don't have any replicators. Other slave servers still thinks as old master is their master and trying replicate from it.
Now insert is waiting indefinitely until synchronous replication happens. This wait is indefinite since now there's no replication happens from new master.

Is this the correct behaviour of pg-pool ?

Now in this setup I need to sync the old master manually and setup the replication. After everything setup pg-pool fails to find primary node. This primary node finding process success, after I comment # - Backend Connection Settings - on pgpool.conf, start pg-pool and again uncomment the connection settings, restart the pg-pool.


texts: procedure to select the first name of the passed id

texts:
I have a table person_name

DESC PERSON_NAME
Name          Null     Type
------------- -------- ------------
ID         NOT NULL NUMBER(9)
FIRSTNAME           VARCHAR2(20)
LASTNAME   NOT NULL VARCHAR2(30)

SELECT * FROM PERSON_NAME
id | firstname | lastname
--------------------------
1  | Bruce     | Wayne
2  | Clark     | Kent
3  | Barry     | Allen
4  | Hal       | Jordan

I have the following procedure

create or replace
PROCEDURE SEARCH_PERSON_USING_ID
(
 IN_ID IN NUMBER
)
AS
  vNAME VARCHAR2(30);
BEGIN
  SELECT pn.firstname INTO vName
    FROM person_name pn
    WHERE pn.id = in_id
     AND  rownum < 2 ;

  dbms_output.put_line('NAME = ' || vNAME);

  EXCEPTION
    WHEN  no_data_found THEN
    vName := null;
END;

It compiles with no problem.
But when I run it, I get the following error

set serveroutput on;
EXECUTE SEARCH_PERSON_USING_SISID(1);

Output:

anonymous block completed
NAME = Bruce

Why the
    anonymous block completed