texts:
I have decided to improve my well-being and in case being successful write a note about this and share it with my peers for free and try to help them improve there well-being as well. But on this road, I need foundational advice from the database practitioners.
Background Description:
In my research, I need to collect a lot of different types of time-series (GDP, Real GDP, Nominal GDP, Consumption, Investment, Percapita GDP, Number of Hours Work, Unit Labor Cost and many more). Right now, I am following and collecting about 155 time-series. Until now, I have been updating them manually as soon as an update was published on statistical web pages. I have been doing this using Excel spreadsheet (like, downloading new excel and then copying and pasting the necessary data point into my excel). However, this is a very daunting task, and it is prone to a lot of errors. Checking and finding the error is another nightmare. So, in total, maintaining this Excel database (updating checking and documenting) takes about 10-12 hours per week approximately.
My Task:

I want to create a database where I will have all these and many more
variables (I want each variable to have a name, description, source
description (ideally with URL included), date of last update etc.) stored.
I also want to be able to update the database automatically from
downloaded Excel or CSV file (These files come in different formats
from various webpages. Some are structure vertically some horizontally).
I also want to have some error checkers for easier debugging the base.
The series are of different frequencies (some are daily, some weekly, some monthly and some quarterly and yet some annual). I want to be able to easily convert from daily to monthly or from monthly to quarterly or vice versa using my own predetermined formula. (being it average or median or whatever the current task requires)
I also want to then later to be able to easily query any variable or several of the variables and create a dashboard in various programs like MATLAB or Python or Julia to visualize dynamics and use them for regression or model estimations in these programs.

Question
For all the tasks described above which database management program would you recommend (ideally, it would be better to be it free and open source)
P.S. I tried writing MATLAB script for automatic updates, but it's very inconvenient.
P.P.S This is a cross-post from Cross Validated in hope to receive the answer somewhere.
Thanks,
Giorgi.
As an example of how my data set looks like please see the following screenshot:


texts: In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

texts:
In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 that beyond my level of expertise. My aim is to tighten up the schema a bit and save myself from dealing with the null case in my code.

A crude way (option 1) to help me solve my problem is to just get all records (and select *) that contain at least one null entry, then eye scan for columns that contain no null entries. I could of course just use Where columnA IS NULL OR columnB IS NULL OR columnC IS NULL, but this gets tedious for tables with lots of columns. Some tables in this database contain more than twenty columns! Also, there's about 500 tables in total. So, general solutions are best.

Here are three options for answers that would satisfy me:

Option 1 : "get me all the records where at least one of the columns is null". I will eyescan for columns with no null entries.
Option 2 : An even better answer would be some script that gets a list of column names that contain at least one null entry. Then I will see which nullable columns are not in this list manually.
Option 3: The best answer would get me a list of column names that are marked as nullable in their schema even though no records exist with a null entry in those columns.

Thanks!


texts: How can I compare each single day of last two weeks in MySQL

texts:
I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last_week' AS week, COUNT(*) AS rows
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous_week' AS week, COUNT(*) AS rows
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days.
Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.


texts: Is it "bad design" to have 2 related tables, each one having a column (named differently) whose value will always be the same in both?

texts:
Imagine that I have the following database structure

TableUser (
   userId integer,
   weeksToLive integer
)

TableCalendar (
   calendarId integer,
   numberOfCells integer,
   userId integer REFERENCES TableUser
)

The point is this will always be the case:

TableCalendar.numberOfCells = TableCalendar.weeksToLive

So I'm wondering if it is harmful or something to have two columns which will always contain the same value, so that the same value can be obtained using different select statements.


texts: Is ORDER BY on two indexed columns will be slow?

texts:
I have multiple-field index on columns a,b,c,d and another unique index on column id. Is my query like below after adding id to ORDER BY slows large or i don't need to worry ? Query plan shows that sort get only 1%.

SELECT *
FROM
  (SELECT *,
          row_number() over (
                             ORDER BY a,b,c,d,id ) AS ROW
   FROM table1
   WHERE /* ... */) a
WHERE ROW > 0
  AND ROW <= 20000;


texts: How to architect Service Broker with three or more systems?

texts:
I'm trying to implement a Service Broker architecture for a business process involving three systems and I want to do it in the spirit of using SB as designed.

System 'A' is an accounting system. Whenever an invoice is 'released' in
that system, a SB conversation starts.
System 'O' is a internally-developed orchestration system. Think of
it as morally equivalent to BizTalk.
System 'C' is a database that has information that needs to be
included on the final invoice to be sent to the customer.

How do I structure a conversation or series of conversations to touch all three systems?

Can I have 'A' submit to 'O', which does a lookup into 'C' - all in the context of a single conversation?


texts: Joining two tables using one to group another

texts:
I have a sqlite database with 2 tables.  Table a has a running list of events and the times they happened. Table b has the start and finish of time each event.

How can I find the minimum event from table a for each group defined in table b?  Schema is described in my SQLFiddle.

I'm trying to get a result like:

id1  id2  time event
c1   d1   2    5
c1   d2   1.2  10
c1   d1   5    8.2


texts:
I have a production database server and a failover database server set up.
All the transactions from the primary production db server is being  replicated(transactional replication with initialize from backup) to the failover database server and it is working fine.

No activities(no reporting) are being done in failover server ,it just gets all the changes from primary production database.
Even the reporting(reporting is minimum) is done from the primary production database server.

The goal is to use it as a failover server and i know that transactional replication is not a good idea for DR and there are other recommended solutions.
But this is the way it was setup by the previous DBA and it sounded okay during that time,i heard.
So if i have to failover,i have to do it manually as i understand by researching on this topic.

There are 3 application servers(app1,app2,app3) managed using the Load Balancer.
One database server(currently primary production server)

I just have a vague idea on how to do failover, by doing my own research.
I have two questions listed below:

Can someone be generous and direct me with steps on how to do the failover to the replicated database server.
Once i failover ,how do i manage the new transactions/changes back to the primary production database server.


texts: How Can I Access the postgres database in a Script Executing pg_dumpall?

texts:
I want to run the following script using my role which includes the postgres database.

pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql

Here are the settings in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local    all            all                                     md5
# IPv4 local connections:
host     all            all            127.0.0.1/32             md5
# IPv6 local connections:
host     all            all            ::1/128                  md5

Initially when I wrote this script I had the methods in pg_hba.conf set to trust. The script ran fine at that time. However when I changed the methods to md5 the script fails with an authentication error when it tries to access the postgres database. The password on my role works when access the databases that I created.

I need to either connect as user postgres to access the postgres database in my script or give access to the postgres database to my role. I would prefer the latter since my script is executed by my role.

I found the following example in the PostgreSQL documentation for pg_hba to allow access to the postgres database to all users with a valid password which is what I want to do.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        md5

I am using a Mac Mini Server where my Rails applications using PostgreSQL are hosted. I have the external IP address (IPv4) that I use to remotely access my server at the colocation site. Is this the IP address I should use in pg_hba.conf or should I use the router IP address? If I add this statement to pg_hba.conf will I need to modify the existing IPv4  statement to accommodate this new rule?


texts: How can I use COUNT in the same query with ROUND() and AVG()?

texts:
I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal place and these values must to be sort by the number of tickets sold. In principle, I want to find the average number of tickets sold for each theater. For this query I must use AVG() and ROUND(). I tried so many different options, but I have different errors. When I tried to run this, this error appeared :
SQL Error: ORA-00937: not a single-group group function
SELECT
    t.id_theater
    , s.id_spectacle
    , ROUND((AVG(COUNT(tickets.id_ticket))), 2) average_tickets_theatre
FROM
    tickets tick
    , theater t
    , spectacles s
WHERE
    tick.id_spectacle = s.id_spectacle
    AND t.id_theater = s.id_theater
GROUP BY
    t.id_theater
    , s.id_spectacle
ORDER BY
    average_tickets_theatre

I tried to add the HAVING clause, but another error appeared:
SQL Error: ORA-00933: SQL command not properly ended

I'm sure that the problem consists in the fact that I'm trying to use COUNT with ROUND() and AVG(), but I really don't know what do to at this point and would appreciate any help.


texts: How should JOINed columns be included in an index?

texts:
We are using SQL Server SE. Using a simple query, I would like to determine which index setup is best. The simple query is:

SELECT
    COUNT(*)
FROM
    (PS LEFT OUTER JOIN LIS
     ON (PS_ID = LIS_PS_ID AND LIS_DATE_PUBLISHED IS NOT NULL))
WHERE
    PS_PER_ID_FIRST = 2000
    AND PS_DATE_INSERTED >= '1/1/2019'
    AND PS_DATE_INSERTED < '1/1/2020'

Notes: Columns ending in _ID are unique identifier INT columns. The PS table holds 2,500,000 records while the LIS table holds 200,000 records. If important, both tables grow organically and slowly, perhaps 20% per year.

It is important for me to get the indexes for this query correct. Normally, I would do this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED, PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID, LIS_DATE_PUBLISHED);

I have never been too familiar with when and why to specify columns as INCLUDEd. I know to specify them as INCLUDEd when they are SELECTed but I'm never sure about the columns in the JOIN statements. In other words, should the indexes instead be like this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED) INCLUDE (PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED) INCLUDE (LIS_PS_ID);

Or, if I'm off my rocker and there is a much smarter way of going about the indexing for this query, please let me know.


texts: How shared nothing and shared everything architectures look like in Oracle?

texts:
Oracle has partitioning and clustering features. Partitioning enables to split table or indexes into multiple tablespaces and store on various servers. So it's done manually. Clustering enables several servers to make operate as one. This IMHO is handled transparently. However, it's possible to have several tables (in different server databases) and group them as one, and use it. From the perspective on table partitioning and table clustering, IMHO both ways implement shared everything because the data is split into the different locations.

The theory about shared nothing architecture is that each database node is independent. But how this looks in practice? Both ways (partitioning and clustering) splits data into different sources and could be also described as horizontal partitioning. However, if we had for example, two different database servers we would need to synchronize the data between them... Anyway, any thoughts on that would be appreciated :)


texts: Can you restore log file backups to a mirror database after failure?

texts:
Purely a theoretical question as I've been considering possible DR scenarios...

In this scenario:

The link between a principle and a mirror server becomes unavailable for a period of time (e.g. network outage)
Incremental log backups are still being taken during this time
Then the principle database fails and is unrecoverable

Is it possible to restore the incremental log backups onto the mirror to bring it up to date and back online, or would you need to start from scratch with a restore of a full backup?

Depending on the size of the database the difference could be hours of downtime so it would be nice to know.  I'm predominantly interested in SQL Server 2008 R2 and 2012 but other people may be interested in other versions if there's a difference.


texts: Select maximum of a count in a grouped clause

texts:
I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)

When I join these tables using:
select YEAR(Sales.saleDate)
     , Vehicles.model
     , count(Vehicles.model) 'Sold'
     , Vehicles.category
  from Vehicles
  JOIN vehicleSold
    on Vehicles.vin = vehicleSold.vin
  JOIN Sales
    on Sales.saleID = vehicleSold.saleID
 group
    by YEAR(Sales.saleDate)
     , Vehicles.model
     , Vehicles.category;

Result is:
+----------------------+-------------+------+----------------+
| YEAR(Sales.saleDate) | model       | Sold | category       |
+----------------------+-------------+------+----------------+
|                 2020 | Altima      |    1 | car            |
|                 2020 | Flying Spur |    2 | car            |
|                 2020 | Lifan E3    |    3 | Electric Moped |
|                 2020 | Ridgeline   |    2 | truck          |
|                 2020 | Shiver      |    4 | motorbike      |
+----------------------+-------------+------+----------------+

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!


texts: Getting information on users payments via subscriptions

texts:
My current table db design looks like this :

- User has one subscription
- One subscription has many payments

Table subscription has a user_id, which is FK to user table.
Table payment has a subscription_id which is a FK to subscription table.

How can I count/see which users have more than one payment or at least sort them by number of payments.

This is what I tried and it doesn't look like it's doing what I want:

SELECT count(subscription.user_id) as cnt, subscription.id FROM payment
INNER JOIN subscription ON payment.subscription_id = subscription.id
INNER JOIN user ON user.id = subscription.user_id
WHERE (payment.refunded_date is null) AND (subscription.deleted_at IS NULL)
group by subscription.id
order by cnt

What is the easiest/optimal way to accomplish this?


texts: Revoke privileges for a user on tables with like names

texts:
Attempting to adapt this solution, but for revoking privileges in this way:
DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'my_schema'
        AND table_name LIKE 'test\_%'
        LOOP
            EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', table_schema, table_name);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

However, I'm getting "column table_schema does not exist". Am I approaching this the right way?
Using postgresql 9.6 (french locale).


texts: db connection uses an anonymous logon instead of the domain user

texts:
I have this PHP code running under IIS:

$serverName = "someServer\someServer";
$connectionInfo = array( "Database"=>"SOME_DB");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.
";
}else{
     echo "Connection could not be established.
";
     die( print_r( sqlsrv_errors(), true));
}

When I try to visit the page, I get the error:

  [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Which is confusing, as the documentation here says "The connection will be attempted using Windows Authentication".

The flow of this is:

User using x Windows account on workstation1 visits this website hosted on server1
server1 executes above PHP code and tries to retrieve data from server2 using y Windows account (y is defined in the IIS settings, under Application Pools->Identity)
Windows account y has access to the database

The IIS pool is running under a Windows domain user "domainadm" which has access to the SOME_DB database and the server it sits on.

Why is it trying to authenticate anonymously and how can I fix this so it runs under the user the IIS pool is running as?

In php.ini:

fastcgi.impersonate = 1;

Checking the Authentication section in IIS, the only options I have are "Anonymous Authentication" or "ASP.NET Impersonation". No Windows authentication?

Anonymous is currently enabled, which gives the error above. If I switch to the ASP.NET one I instead get this error when visiting the page:

  500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.


texts: SQL query - reference another entity's property from property on another table

texts:
SELECT account, role from roles where username=?

My roles table is structured as follows:

@Entity(name = "roles")
class Role{
    private Long id;
    private String role;
    private Long account;
}

My account table is structured as follows:

@Entity(name = "accounts")
class Account{
    private Long id;
    private String username
    private String property1;
    private String property2;
    //etc.
}

The problem is I only have the username as an input (the question mark parameter below) to my function:

SELECT account, role from roles where username=?

Is there any way I can restructure the above query to get the account and role columns from the roles table?

SELECT account.userame as account, role from roles where username=?

I know the above is a complete hack and won't work... I'm afraid my SQL knowledge isn't sufficient.

Finally, yes, I only have access to the username parameter for doing my search...


texts: Revoke User Access to all System Information Functions on Postgresql

texts:
Is there a way to remove access to all system information functions for a given user (which is already limited to read only access) in Postgres?

For example, a user could normally run pg_conf_load_time() or SELECT current_setting('datestyle');.

Normally you could do do

REVOKE ALL ON FUNCTION current_setting(text) FROM my_user;

but I get no privileges could be revoked for "current_setting".

Is there a way to disallow access to all functions on these pages?

https://www.postgresql.org/docs/current/functions-admin.html
https://www.postgresql.org/docs/current/functions-info.html


texts: Using TDE and SQL Server 2016 AlwaysOn Availability Groups

texts:
We currently use SQL Server 2012 AG are in process of migrating to SQL 2016. We use EMC SAN for storage and it provides the data encryption at rest on hardware level. Some folks are thinking about start using TDE that also provides encryption at rest only that we already have.

While it's not so difficult to implement TDE, it, in my opinion, creates a lot headache in terms of managing and administrating. From your experience, is it good idea to use TDE in our situation? Is it worth? How well TDE compatible with AlwaysOn AGs? I read, for example, you can't use AG GUI to do anything. Everything has to be done through scripts.


texts: Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

texts:
I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will execute it with sp_ExecuteSql.

Is SQL Server able to paraellelize each batch of queries and is there a way I can tell when it will parallelize them? (I guess I could print my dynamic SQL, and copy the query batches into a new worksheet and look at the execution plan to verify if the optimizer is planning on parallelizing them.)

Typically I'm trying to aggregate the data from each database into one temp table I declare outside of the dynamic SQL. Here's an example:

DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

DROP TABLE IF EXISTS #Results
SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue
INTO #Results

SELECT @DynamicSql = @DynamicSql +
'
    USE [' + T.DatabaseName + '];

    INSERT INTO #Results
    SELECT ' + CAST(T.DatabaseId AS VARCHAR(10)) + ' AS DatabaseId, T.ResultId, T.ResultValue
    FROM SomeTable AS T
    WHERE T.SomeCondition = ''SomeValue'';
'
FROM MyToolkit.dbo.vwDatabaseInstances

--PRINT @DynamicSql
EXEC sp_ExecuteSql @DynamicSql

SELECT *
FROM #Results


texts: Why is user: system doing an hourly backup of my SQL databases?

texts: I've got a pretty standard maintenance plan set up for my SQL Server databases; yet in addition to that plan, all of my databases, user and system, are backed up every hour by user: System... where is that getting initiated from and how can I turn it off?  There is also an associated MSMQ backup of components: registry, sysq, https, etc during the same time frame.


texts: Timeout while using table valued parameter

texts:
I'm trying to find the cause for this error while calling a stored proc with a table valued parameter:

  Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

My objective is to load sanitized data from a 3rd party app into a data-warehousey location for in-house apps to consume. I don't have an "updated" flag in the source, so I'm using a data flow to push all of the data into a recordset, then firing a script task to call a MERGE proc with TVP.

I've used this pattern successfully several times, but this data set is by far the largest--over 1m rows (though not terribly wide). I can always truncate and reload the table every time within an acceptable time frame, but that just seems awfully wasteful on I/O.

I looked at the settings in the ADO.NET connection, but the timeouts there don't seem like they should apply. I'm considering slicing the recordset into smaller chunks and looping over the proc, but without a better understanding of the problem, I suspect that would just cause problems when the data set grows enough to make the chunks fail.

Proc:

alter proc [sis].[Attendance_Merge]
    @data sisAttendance readonly
    as begin
if object_id('tempdb..#data') is not null drop table #data;
create table #data (
    [SchoolYear] smallint not null,
    [Student] [varchar] (10) NOT NULL,
    [Date] [date] NOT NULL,
    [Period] [varchar] (10) NOT NULL,
    [Reason] [varchar] (32) NOT NULL,
    [Funding] [varchar] (1) NOT NULL,
    [StateReported] [varchar] (1) NOT null,
    [rn] int not null
    primary key clustered (SchoolYear,Student,[Date],Period,rn)
    );
insert into #data
select
    *
    ,rn = row_number() over(
        partition by
            SchoolYear
            ,Student
            ,[Date]
            ,Period
        order by
            Student
        )
    from @data
    ;
with att as (
    select
        *
        ,rn = row_number() over(
            partition by
                SchoolYear
                ,Student
                ,[Date]
                ,Period
            order by
                Student
            )
        from sis.Attendance
    )
merge into att t
    using #data s
        on t.SchoolYear = s.SchoolYear
        and t.Student = s.Student
        and t.[Date] = s.[Date]
        and t.Period = s.Period
        and t.rn = s.rn
    when matched
            and (t.Reason <> s.Reason
                or t.Funding <> s.Funding
                or t.StateReported <> s.StateReported
                )
        then update set
            t.Reason = s.Reason
            ,t.Funding = s.Funding
            ,t.StateReported = s.StateReported
    when not matched by target
        then insert (
            SchoolYear
            ,Student
            ,[Date]
            ,Period
            ,Reason
            ,Funding
            ,StateReported
            )
        values (
            s.SchoolYear
            ,s.Student
            ,s.[Date]
            ,s.Period
            ,s.Reason
            ,s.Funding
            ,s.StateReported
            )
    when not matched by source
        then delete
    ;
end


texts:
Thanking in advance I ask for help in resolving an issue with Oracle database starting.
Database was working correctly before linux machine reboot, but after reboot through reboot terminal command it could not start correctly.
To resolve this issue I've tried:

sqlplus / as sysdba
SQL> startup force
ORACLE instance started.

Total System Global Area 2147482944 bytes
Fixed Size                  9137472 bytes
Variable Size             520093696 bytes
Database Buffers         1610612736 bytes
Redo Buffers                7639040 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/CDB1/system01.dbf'

SQL> recover datafile '/u02/oradata/CDB1/system01.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00742: Log read detects lost write in thread 1 sequence 424 block 78120
ORA-00312: online log 1 thread 1: '/u02/oradata/CDB1/redo01.log'

What steps could I perform to get my database working correctly?

OS: Oracle Linux 7.6, DBMS: Oracle DB 19c

Updated. Additional tries, that did not help, but made things worse. I think, it was possible to recover the db somehow using redo01.log.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 7741502 generated at 05/24/2019 11:21:47 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/19c/dbhome_1/dbs/arch1_423_1007742121.dbf
ORA-00280: change 7741502 for thread 1 is in sequence #423

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/product/19c/dbhome_1/dbs/arch1_423_1007742121.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

ORA-00308: cannot open archived log
'/u01/app/oracle/product/19c/dbhome_1/dbs/arch1_423_1007742121.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/CDB1/system01.dbf'

SQL> Alter database open;
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> Alter database open restlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 13270
Session ID: 4 Serial number: 28040

SQL> startup
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE

SQL> quit

sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area 2147482944 bytes
Fixed Size                  9137472 bytes
Variable Size             520093696 bytes
Database Buffers         1610612736 bytes
Redo Buffers                7639040 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 14122
Session ID: 462 Serial number: 58943

During these tries I've broken something, because of ORA-03113


texts: Convert Text Column Web Service Data to Columns

texts:
I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 data3=value3 moredata4=value3..."

The pairs represent fields/columns from an external database integrated thru a rest service. God knows why it's stores in this way, but I need to parse this column into multiple columns. I cannot figure out how to do this in an efficient and clean way.

Something to note: not all records/rows in the table have a value for every item in the combined column. So you might see 'data1=value1 data2= data3=data3...' also not all 'firlds/columns' in the concatenated pairs start with 'data', some might say 'tranamt=50 coupon=2bb2' for example. Also to note, the 'space' looking character between pairs is actually chr(10) not a space.

I'd really appreciate anyone's help on this. I'm stumped. I've researched this endlessly on stackexchange and Google, and not been able to find anything that helps me with this unique scenario. I've split delimited columns before, but not with a 'field/column' and value pair. I'm also new to oracle. This is in an oracle db.

Thanks!


texts: JOIN on Microsoft Access derived table not working

texts:
Can you please help me why my below SQL is not working in Access 2010?

SELECT
  Table2.Product,
  Table2.Color,
  SEQMAX,
  Table1.Attribute
FROM
(
  Table2

  LEFT JOIN
  (
    SELECT
      Table1.Product,
      MAX (Table1.Seq) as SEQMAX
    FROM Table1
    GROUP BY Table1.Product
  ) AS MAXTABLE
  ON  Table2.Product = MAXTABLE.Product
)
LEFT JOIN  Table1
  ON MAXTABLE.SEQMAX = Table1.Seq
  AND Table2.Product = Table1.Product ;

It is fine until I add the last line AND Table2.Product = Table1.Product both pieces of the AND worked fine separately, however I can't figure out why they don't work when combined.

I'm getting an error: JOIN expression not supported.


texts: What is the impact on a running database when OS date is changed and set to past

texts:
My SAP back end db is db2. Need to understand the impact on this running database (db2 under HPUX) if I change the OS date and set it to a past date please.

Thanks.


texts:
I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?

My table definition:

CREATE TABLE mytable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
)


texts: SQL Error while restoring big database in Mysql

texts:
I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB.

When I try to restore it in an UBUNTU machine I got this error.


texts: How do I insert tens of thousands of rows without locking?

texts:
I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There can be up to 6 of these processes (perl) running simultaneously and each may have over 50,000 insert statements, and subsequently, the tables are getting locked.

What I am trying to figure out is what causes locking? Am I better off creating a multi-insert with, say, 100 rows at a time and doing them end to end? What do I use for guidelines?

The DB server has 100GB RAM and 12 processors. It is very lightly used but when these inserts come in, everyone freezes up for a couple minutes which disrupts peopel running reports, etc.

Thanks for any advice. I know I need to stagger the inserts, I am just asking what is a recommended way to do this.


texts: Design and implement role based access control in DynamoDB

texts:
I am learning DynamoDB and trying to implement role-based access control in DynamoDB. Access control is a huge part of the application.
ERD looks something like this:

A little explanation:

Groups will be defined to collectively assign the same Rights on Sites to Users

Sites are hierarchical(Sites -> Zones -> Locations )and Right on a Site higher up in the hierarchy automatically gives Rights for all Sites below it - but there is an option to "override" Rights for a specific Site

Every Customer has many Users and a User can be in multiple CustomerAccounts

Basic Access patterns:
Get account info by customer_id
Get account info by account_id
Load user info by user_name
Get customers that a user is a member of
Get/update groups a user is in
Get/update sites a user has access to
Get/update users in a group
Get/update sites in a group


texts: Recover Mysql data from formated hard disk

texts:
One of my machine got crashed last day and i ended up reinstalling windows on that machine. It had a mysql server in it. Is there a way to recover mysql data from that machine now . There are couple of tables which has some important data for which backup was not there.

any help would be greatly appreciated.


texts: How can we decrypt data knowing the symmetric key used for the encryption?

texts:
In this article is explained how to decrypt a symmetric key. For example:

SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc,
       KE.crypt_type_desc,
       COALESCE(C.name,AK.name,PSK.name) AS protector_name,
       KE.crypt_property AS encrypted_key,
       COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property),
                DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key
  FROM sys.key_encryptions AS KE
  JOIN sys.symmetric_keys AS SK
    ON KE.key_id = SK.symmetric_key_id
  LEFT JOIN sys.certificates AS C
    ON KE.thumbprint = C.thumbprint
  LEFT JOIN sys.asymmetric_keys AS AK
    ON KE.thumbprint = AK.thumbprint
  LEFT JOIN sys.symmetric_keys AS PSK
    ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));

It can be tested using the following query:

--
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'smGK_MasterKeyPassword@';

--
CREATE CERTIFICATE [CERT_V001]
WITH SUBJECT = 'User for protecting SM symetric keys.'

--
CREATE SYMMETRIC KEY [SK_SecurityUsers_V001]
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE [CERT_V001]
GO

DECLARE @Email NVARCHAR(128) = 'sometest@google.com';
DECLARE @EmailEncrypted VARBINARY(256);

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT @EmailEncrypted = ENCRYPTBYKEY(KEY_GUID('SK_SecurityUsers_V001'),@Email);
SELECT @EmailEncrypted;

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EmailEncrypted));

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];

--DROP SYMMETRIC KEY [SK_SecurityUsers_V001];
--DROP CERTIFICATE [CERT_V001];
--DROP MASTER KEY;

I am wondering, does this mean the data is not protected at all?

In the article is said, that:

  However, for password-protected and symmetric-key-protected keys this
  sadly does not work.

I guess this means I need to use one of these types of encryption in order to be sure the data is protected?


texts:
Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later?

I would like the conceptual functionality of SelectedSubsetId below.

CREATE TABLE dbo.SelectedSubset
(
    ItemSetId INT NOT NULL FOREIGN KEY REFERENCES dbo.ItemSet(ItemSetId),
    SelectedSubsetId INT NOT NULL IDENTITY(1,1) OVER(PARTITION BY ItemSetId),
    PRIMARY KEY CLUSTERED (ItemSetId, SelectedSubsetId )
)

Where SelectedSubsetId would start at 1 and increment by 1 for each ItemSetId entered.  Another table would would foreign key this primary key and add the item ids of the set

CREATE TABLE dbo.SubsetItems
(
    ItemSetId INT NOT NULL,
    SelectedSubsetId INT NOT NULL,
    ItemId INT NOT NULL,
    FOREIGN KEY (ItemSetId, SelectedSubsetId) REFERENCES dbo.SelectedSubset(ItemSetId, SelectedSubsetId),
    FOREIGN KEY (ItemSetId, ItemId) REFERENCES dbo.ItemSetItems(ItemSetId, ItemId),
    PRIMARY KEY CLUSTERED (ItemSetId, SelectedSubsetId, ItemId)
)

In this concept SelectedSubsetId is only defined by its ItemIds. It has no attributes of its own that warrant creating a table to hold additional information.


texts:
I have a table that holds timetracking data of users and looks somewhat like this:

projectid activityid worklocationid year  week  day         hours
1         22         21             2014  37    09/16/2014  8
2         22         4              2014  37    09/15/2014  8.5

So if a user saves hours on one project for every day of the week, there are 5 rows in the database that only differ in the "day" and "hours" columns.
I need to display this data on a webpage in a timetable format, without having to query the database for each cell to get the hours for a specific day. I'd like to generate a view that has one row per week for one project/activity/worklocation combination:

projectid activityid worklocationid year  week mon tue wed thu fri sat sun total
1         22         21             2014  37   8   8.5 9   8   8           41.5

I tried solving this with subselects and putting the days and hours values into one column respectively, separated by semicolons, but failed miserably because the table has over one million records.

My current approach would be using an OLAP cube, but I'm facing an issue:

There might be one entry for one week, e.g. on Monday, or one each for Monday and Thursday, or five for each day of the week, or none at all.
So if I want to save them in an ordered format, I have to determine which row belongs to which day of the week. Is there a way to do this that scales up to this large amount of records?

We're using Oracle 11g.

Please feel free to point out if I missed to write down additional information.


texts:
I actually have 2 Databases for one application under a SQLServer 2012 ALwaysON,
I want to add another Database for a different application... It is recommended to create a new AlwaysON AG Group?

I would like to know if is possible to have something like this:

AG Group 1
Server 1

DBAPP1(PRIMARY)
DBAPP2(PRIMARY)
DBAPP3(SECONDARY)

AG Group 1
Server 2

DBAPP1(SECONDARY)
DBAPP2(SECONDARY)
DBAPP3(PRIMARY)

Is this possible? Or just dumb?

Thanks!


texts: What is making MySQL stay in strict mode

texts:
New Mac

El Capitan 10.11.2

mysql-5.7.10-osx10.10-x86_64.dmg

I have to work with a database that has default dates "0000-00-00" but MySQL is in strict mode and chokes on them.

I can't find a way to switch off strict mode. I have
  [mysqld]
  sql-mode="";
in ~/my.cnf, but this makes no difference. I wonder if something is resetting it further up the food chain but I have no idea where to look. I have tried /usr/local/mysql/my.cnf. I have tried with the files called .my.cnf. The OS won't let me create /usr/my.cnf (an Apple security feature apparently).

Where else might I look; what else might I try?

Also some websites say sql_mode (underscore, not hyphen); which is correct?

Thanks


texts: How to reduce size of a large SQLite database with series data

texts:
I'm considering to use SQLite database for a C# application which deals with large volumes of data series. The data is currently in several CSV files of up to 20GBs in size each and of the following format:

2019.07.31 00:00:03.855,1.11568,1.11571,3,0

I was expecting to reduce the size considerably when migrating to SQLite but to some reason am getting some different results.

The example string in CSV format takes 44 bytes (43 chars + new line). As far as I understood the SQLite types definitions described at sqlite.org it should take about the same in the database:
23 byte datetime text + 8*2 reals + 1*2 integers = 41 bytes

The dateTime can be stored as 8-byte integer (c# DateTime.Ticks) which should reduce the size to about 26 bytes per one line which is almost twice less than in CSV format.

For an experiment I tried importing 142,157 lines of data. The CSV file is about 6kk bytes in size but the resulting DB file is about 5kk which is almost the same. Compression (available in DB Browser for SQLite) does not change anything.

The table schema is:

CREATE TABLE "Data" ( "dateTime" INTEGER, "value1" REAL, "value2" REAL, "value3" INTEGER, "value4" INTEGER )

An example row looks like this:

637001280038550000 1.11568 1.11571 3   0

Is it the SQLite internals which make that big overhead in size?
How can I reduce the size?

As a side note, setting Primary Key on the dateTime field reduces the size of the example data file by 0.3kk bytes but adding unique constraint increases it by 3kk bytes making it about 8kk in total which is much bigger than the CSV one.

Update

output from sqlite3_analyzer

w/t PK and contraints
Size of the file in bytes......................... 5242880
Bytes of user payload stored...................... 4349676     83.0%
Bytes of payload.................................. 4349852     83.0%
Bytes of metadata................................. 858115      16.4%

with PK
Size of the file in bytes......................... 4976640
Bytes of user payload stored...................... 3212420     64.5%
Bytes of payload.................................. 3212622     64.6%
Bytes of metadata................................. 1733838     34.8%

with PK and Unique constraint
Size of the file in bytes......................... 8134656
Bytes of user payload stored...................... 3212420     39.5%
Bytes of payload.................................. 3212622     64.6%
Bytes of metadata................................. 1733838     34.8%
*** Page counts for all tables and indices separately
TABLE............................................. 1214        61.1%
SQLITE_AUTOINDEX_TABLE_1.......................... 771         38.8%

So it turns out that the internals take 16%, PK takes additional 19% but still reduces the total size, and the unique contraint takes almost 40%!

The question remains valid. Is it possible to recude the size of the database or that is all one can get from it?


texts: Using SQL Server 2016 R Services over a standalone installation of Microsoft R Server

texts:
I've been experimenting with SQL Server 2016 and its R Services. I also have a standalone Microsoft R Server installed on my machine.
What is a good use-case to use SQL Server R Services over a standalone Microsoft R Server, since both feature properties of an enterprise scale R platform.


texts: How to order by specific column without breaking groups?

texts:
I have a table like below and I want to order this table by date without breaking groups.
id    group       date
1 |   group1 | 2011-11-24
2 |   group2 | 2011-08-20
3 |   group1 | 2011-03-11
4 |   group3 | 2011-05-05
5 |   group3 | 2012-01-01

I only consider the latest updated group element when ordering by date.
I want to get order like;
id    group       date
5 |   group3 | 2012-01-01
4 |   group3 | 2011-05-05
1 |   group1 | 2011-11-24
3 |   group1 | 2011-03-11
2 |   group2 | 2011-08-20


texts: MySQL query to select a record from each group that meets a user-defined criterion

texts:
I am looking for a query that selects a record from each group that meets a user-defined criterion.  I can explain with the following illustrative table:
CREATE TABLE sample_table
(
  id INT UNSIGNED AUTO_INCREMENT,
  categoryID INT,
  weight INT,

  PRIMARY KEY(id)
);

The table is populated as follows:
INSERT INTO sample_table(categoryID, weight) VALUES(1, 3), (1, 5), (1, 2), (2, 5), (2, 3), (2, 9), (3, 5), (3, 3), (3, 3);

A simple GROUP BY categoryID query returns the first record in each group, as shown below:
SELECT * FROM sample_table GROUP BY categoryID;
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  1 |          1 |      3 |
|  4 |          2 |      5 |
|  7 |          3 |      5 |
+----+------------+--------+

To return the last record in each group we can use the approach suggested here:
SELECT * FROM sample_table WHERE id IN (SELECT MAX(id) FROM sample_table GROUP BY categoryID);
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  3 |          1 |      2 |
|  6 |          2 |      9 |
|  9 |          3 |      3 |
+----+------------+--------+

However, what I want to do is to select the record which the highest value for the weight field in each group.  My output, therefore, should be:
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  2 |          1 |      5 |
|  6 |          2 |      9 |
|  7 |          3 |      3 |
+----+------------+--------+

Kindly suggest the GROUP BY categoryID query which will yield the above output.


texts: How do I encrypt data to be decrypted on a linked server? SQL

texts:
I have been searching for 2 days for the solution to this, but every solution does not apply to my situation.

I have 2 SQL servers: DataServer and InterfaceServer set up as linked servers. DataServer does all of our data processing. InterfaceServer pulls only the data it needs from DataServer. Each server has its own encryption process and our compliance department does not want them to use the same passwords/certficates/etc... Due to these restrictions, I am unable to execute a stored procedure that does the decryption for me.

I have tried creating views with the DecrypByKeyAutoCert() function as well as creating views with SELECT ... FROM OPENQUERY() to execute the stored procedure that does the decryption, but they always return NULL values for the decrypted data. If I run them on the DataServer itself, it returns the data fine.

Is there a way to create an assymmetric key on InterfaceServer, use the public key to encrypt data in a table on DataServer, then decrypt it on the InterfaceServer after copying everything over?


texts: What could be the cause of invalidation of "sys" views?

texts:
I had never experience anything like this but one of the scripts containing
select count(*) from user_tables where table_name = 'XXX';

started to throw

PL/SQL: ORA-04063: view "SYS.USER_TABLES" has errors

I use visual tool to look into it - and here it is

And I see many views are invalidated. What is the possible cause for this? I've never seen sys.views invalid.
What I did is ran this to create alter scripts
select 'alter view SYS.' || object_name || ' compile;' from user_objects where object_type = 'VIEW' and  status = 'INVALID';

Created 707 rows. Ran them, still 202 remains problematic. Run again, still 202.
And here is error sample
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-04063: view "SYS.DBA_OBJECT_TABLES" has errors
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Addition Info
I started to look into user_table definitions. And I tested tables in the FROM.
Finding - select * from sys.deferred_stg$ - table does not exist


texts: Unable to add primary key to innodb table without one

texts:
I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows:

CREATE TABLE `my_wonky_table` (
  `id` bigint(20) unsigned NOT NULL,
  `login` varchar(127) DEFAULT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `ip` varchar(32) CHARACTER SET ascii DEFAULT NULL,
  `val_1` int(10) unsigned DEFAULT NULL,
  `val_2` varchar(127) DEFAULT NULL,
  `val_3` varchar(255) DEFAULT NULL,
  `val_4` varchar(127) DEFAULT NULL,
  `val_5` int(10) unsigned DEFAULT NULL,
  KEY `my_wonky_table_id_idx` (`id`),
  KEY `my_wonky_table_timestamp_idx` (`timestamp`),
  KEY `my_wonky_table_val_1_idx` (`val_1`,`id`),
  KEY `my_wonky_table_val_2_idx` (`val_2`,`id`),
  KEY `my_wonky_table_val_4_idx` (`val_4`,`id`),
  KEY `my_wonky_table_val_5_idx` (`val_5`,`id`),
  KEY `my_wonky_table_ip_idx` (`ip`(16),`id`),
  KEY `my_wonky_table_login_idx` (`login`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Now, because it has no primary key, nor a fully unique index, MySQL adds a GEN_CLUST_INDEX (that's taking up quite some space, but that's a different matter).

Note that even though there is a column called id, it's not actually a unique entry. For this reason, I want to add an (auto incremented) column, that, combined with the id column, should act as the primary key.

However, I appear to be unable to add an auto_incremented column; I suspect this is because of the 'secret' column MySQL adds, because of the error message saying I already have an auto-incremented column, even though I don't believe I do.

The following:

ALTER TABLE my_wonky_table
    ADD COLUMN count SMALLINT NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (id, count);

yields:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Any ideas?


texts: I'm coming from SQL Server background and getting into a project that uses MySQL. Is there any way to search the metadata of a MySQL database like there is with the Redgate SQL Search tool for SQL Server?


texts: How can I cancel index creation in SQL Server

texts:
How can I cancel an index creation?

It is still running and will run for a long time, because by accident I have created a clustered index; and non clustered indices are recreating on a really huge table.


texts: How would I track all price changes in a db in order to get the price of 'x' product on 'y' date

texts:
I would need to track a products price changes so that I can query the db for a product price at a given date. The information is used in a system that calculates historical audits so it must return the correct price for the correct product based on the date of purchase.

I would prefer to use postgres in building the db.

I need with the design of the database but any and all best practice suggestions are also welcome.


texts: PostgreSQL Common Table Expression: Ambiguous Column

texts:
I have a Common Table Expression which joins two tables with some column names in common:

CREATE TABLE first(
    id serial PRIMARY KEY,
    data VARCHAR,
    date DATE
);
CREATE TABLE second(
    id serial PRIMARY KEY,
    key INT REFERENCES first(id),
    data VARCHAR,
    date DATE
);

WITH cte AS
    (SELECT * FROM first JOIN second ON first.id=second.key)
SELECT data FROM cte;

This, of course, results in an error because field data is ambiguous.

I know that it would be possible to extract the ambiguous columns with the CTE and give them unique names.

The question is, is it possible resolve this ambiguity from the main SELECT statement? Alternatively, is there some notation I can use within the CTE to make this impossible without actually creating new names?

Edit

A number of comments suggest using the wild card is not appropriate. I only use it here to simplify the query — never in a serious query. Here is another version:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data, s.data
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT data FROM cte;

I know that I can resolve the issue by aliasing in the CTE:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data as fdata, s.data as sdata
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT fdata FROM cte;


texts: Turn off ANSI_WARNINGS for heterogenous queries

texts:
We have a Stored Procedure with many linked-server queries. When executing it displays a Warning:

  Warning: Null value is eliminated by an aggregate or other SET
  operation.

To suppress this, I  tried :

  SET ANSI_WARNINGS OFF

But that threw this error :

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
  to be set for the connection. This ensures consistent query semantics.
  Enable these options and then reissue your query.

All articles I looked at mention setting ANSI_WARNINGS to ON to get it to work.
But I Absolutely do not want to see those Warnings, because I'm sending query results as attachment in sp_send_dbmail which has that Warning message right at the top of attachment.

Is it possible to suppress the warnings for heterogenous queries ?


texts: How to both reference and return a result set from a stored procedure in mysql?

texts:
I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both?
BEGIN
   #return this and also use it in the following query
   select id, name, table2_id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id = table2.id from the previous result set
END


texts: Maxscale read write split and route filter

texts:
I have trouble to forward all SELECT requests to the slave in a MySQL master-slave architecture.

I'm using maxscale's read-write router, which does its job except for

  all statements using temporary tables

being redirected to the master (github doc). But I want those select requests (mostly SELECT COUNT...) being executed on the slave because those are the heaviest ones in terms of cpu load.

I've set up a route filter, supposed to redirect all SELECTs to the slave database, but it isn't working, though maxscale has been restarted and the filter has shown up in the filter chain.

My maxscale configuration:

[maxscale]
threads=4

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=master-db, slave-db
user=root
passwd=*
monitor_interval=10000

# =============== Filters ================ #
[SelectToSlaveFilter]
type=filter
module=namedserverfilter
match=select*
options=ignorecase
server=slave-db

# =============== Services ================ #
[RW Split Router]
type=service
router=readwritesplit
servers=master-db, slave-db
user=client
passwd=*
max_slave_connections=1
enable_root_user=1
filters=SelectToSlaveFilter

[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli

# ===============  Listeners ================ #
[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4446
socket=/var/lib/maxscale/rwsplit.sock

[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
address=127.0.0.1
port=4442

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603

# =============== Servers ================ #
[master-db]
type=server
address=X.X.X.X
port=3306
protocol=MySQLBackend

[slave-db]
type=server
address=127.0.0.1
port=3306
protocol=MySQLBackend

Additional information that confirms the filter is used in the routing process:

maxadmin -pmariadb 'show session 0x54ae060'
Session 1840 (0x54ae060)
    State:          Session ready for routing
    Service:        RW Split Router (0x54992c0)
    Client DCB:     0x54aff50
    Client Address:     root@localhost_from_socket
    Connected:      Thu Nov 26 09:10:40 2015
    Idle:       4 seconds   Filter: SelectToSlaveFilter
        Match and route:            /select */ -> slave-db
        No. of queries diverted by filter:  3
        No. of queries not diverted by filter:      1

One of the heavy queries still showing up on the master, even with filtering:

Copying to tmp table
SELECT COUNT(*) as nb, DATE(`Comment`.`date_update`) as day FROM `table`.`comments` AS `Comment` WHERE `Comment`.`date_update` >= '2015-11-26 00:00:00' AND `Comment`.`date_update` <= '2015-11-26 23:59:59' GROUP BY DATE(`Comment`.`date_update`)

Maxscale error log:

---     Logging is enabled.
2015-11-26 09:56:44   Error: Failed to obtain address for host ::1, Address family for hostname not supported
2015-11-26 09:56:44   Warning: Failed to add user root@::1 for service [RW Split Router]. This user will be unavailable via MaxScale.
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: root@X.X.X.X for database: (null)
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: root@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: cmon@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Error: Failed to set socket options. Error 95: Operation not supported


texts: standalone mysql 5.7 instance on preemptible node, which settings can make mysql run in the most fault-tolerant way possible?

texts:
Simplifying, I have a standalone mysql instance running on a docker container hosted on a preemptible node.
That means that at least ever 24 hours, the underlying node is killed, and a clean shutdown is never guaranteed.
Before going this route, I tested the setup by simulating this scenario on different loads, also write operations, and killing the underlying nodes by triggering a kernel panic: no issues even after thousands of restarts.
On the real world, from time to time - let's say once a month over 3000 mysql instances - a DB gets corrupted, and it needs recovery (force recovery, full dump, reload of the dump).
What is the best set of options I could configure to make sure mysql operates in a way where, even with frequent kills of the server, it will not write inconsistent data? Sacrificing performance is not an issue.
This is the config it currently has (mysql is running on docker, so aio = OFF):
max_connections = 60
innodb_buffer_pool_size = 16M
tmp_table_size = 4M
key_buffer_size = 8M
query_cache_size = 4M
query_cache_limit = 512K
thread_stack = 128K
performance_schema = 0
show_compatibility_56 = 1
innodb-use-native-aio = OFF

Edit: I am looking for my.cfg settings to make mysql as crash-proof as possible, not architectural solutions


texts: Functional Index LOWER PostgreSQL not working

texts:
The below SQL query runs in 2s:

SELECT
    *
FROM
    fb_name
    INNER JOIN fb_kg ON mid = subject_mid
    WHERE
        LOWER(alias) = LOWER('Polaski')
        AND fb_kg.relation = 'location/location/containedby'

The below SQL query runs in 2ms:

SELECT
    *
FROM
    fb_name
    INNER JOIN fb_kg ON mid = subject_mid
    WHERE
        alias = 'Polaski'
        AND fb_kg.relation = 'location/location/containedby'

The difference between the two queries is the inclusion of the LOWER function.

There has been an index created for both queries:

CREATE INDEX fb_name_lower_alias ON fb_name (lower(alias));
CREATE INDEX fb_name_mid_lower_alias_index ON fb_name (mid, lower(alias));
CREATE INDEX fb_name_alias ON fb_name (alias);
CREATE INDEX fb_name_mid_alias_index ON fb_name (mid, alias);

EXPLAIN for LOWER:

"Gather  (cost=247515.73..249133.22 rows=5708 width=70)"
"  Workers Planned: 2"
"  ->  Merge Join  (cost=246515.73..247562.42 rows=2378 width=70)"
"        Merge Cond: ((fb_kg.subject_mid)::text = (fb_name.mid)::text)"
"        ->  Sort  (cost=205352.30..205794.73 rows=176972 width=44)"
"              Sort Key: fb_kg.subject_mid"
"              ->  Parallel Bitmap Heap Scan on fb_kg  (cost=14116.24..184480.39 rows=176972 width=44)"
"                    Recheck Cond: ((relation)::text = 'location/location/containedby'::text)"
"                    ->  Bitmap Index Scan on relation_index  (cost=0.00..14010.06 rows=424733 width=0)"
"                          Index Cond: ((relation)::text = 'location/location/containedby'::text)"
"        ->  Sort  (cost=41163.43..41232.45 rows=27611 width=26)"
"              Sort Key: fb_name.mid"
"              ->  Bitmap Heap Scan on fb_name  (cost=750.54..39126.71 rows=27611 width=26)"
"                    Recheck Cond: (lower((alias)::text) = 'polaski'::text)"
"                    ->  Bitmap Index Scan on fb_name_lower_alias_varchar_pattern_ops  (cost=0.00..743.64 rows=27611 width=0)"
"                          Index Cond: (lower((alias)::text) = 'polaski'::text)"

Other EXPLAIN:

"Nested Loop  (cost=11.45..13502.52 rows=2 width=70)"
"  ->  Bitmap Heap Scan on fb_name  (cost=4.64..48.23 rows=11 width=26)"
"        Recheck Cond: ((alias)::text = 'Polaski'::text)"
"        ->  Bitmap Index Scan on fb_name_alias_index  (cost=0.00..4.64 rows=11 width=0)"
"              Index Cond: ((alias)::text = 'Polaski'::text)"
"  ->  Bitmap Heap Scan on fb_kg  (cost=6.81..1223.04 rows=8 width=44)"
"        Recheck Cond: ((subject_mid)::text = (fb_name.mid)::text)"
"        Filter: ((relation)::text = 'location/location/containedby'::text)"
"        ->  Bitmap Index Scan on subject_mid_index  (cost=0.00..6.81 rows=316 width=0)"
"              Index Cond: ((subject_mid)::text = (fb_name.mid)::text)"

CREATE TABLE definitions

CREATE TABLE fb_kg
        (object_mid varchar NOT NULL,
        relation varchar NOT NULL,
        subject_mid varchar NOT NULL,
        PRIMARY KEY(object_mid, relation, subject_mid));

CREATE TABLE fb_name
    (mid varchar NOT NULL,
    alias varchar NOT NULL,
    PRIMARY KEY(mid, alias));

How come with the same indexes the LOWER function is about 60x slower?


texts:
I apologize if this question has been asked before, I am not exactly sure how to phrase it.

I am currently creating a SQLite database for use in a NodeJS application. The DB will need to be read from frequently and written to somewhat frequently.

I am wondering if there is a significant difference between running the database from a file on disk or as a "standalone" (i.e. as a DB running inside of a Docker container that the application connects to)?


texts:
Activity Monitor can show real time the overall IO performance in MBytes/s on a server, but cannot show the same per session level.

I know about DMVs, and sp_who2 to retrieve session level io consumption , but there, its only the number of reads/writes can be extracted, not the actual Megabytes/s.

How can I see real time, session level io performance in MBytes/s?


texts: Taking backup of all the databases in SQL Server

texts: I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I am looking for a Batch script or DB script which will automatically take the backup of all the 250 databases. Can anyone please help on this?


texts: MySQL limits on huge number return zero rows

texts:
I have table about 1,000,000 rows. this query work well:

SELECT * FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)
LIMIT 2000,10

But this query return zero rows:

SELECT * FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)
LIMIT 800000,10

What's my problem? Any configure or etc?


texts: MySQL: How to troubleshoot when simple queries are not returning anymore?

texts:
This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.

When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.

mysqld.log shows nothing important and when I kill my SQL query I get: 160712  7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.

Disk space is fine, plenty of space. CPUs are fine too.

Basic mysqlcheck on all databases all returned OK.

mysqltuner results are the following one hour after being restarted:

[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)

I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?

Thank you in advance


texts: Stopping SQL Server Agent: Effect on runnings jobs

texts:
On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day.

Now and then, the server needs to be restarted (patching etc). Is it safe to right-click on SQL server agent and select 'stop'? The SSIS-packages are not that well-built and if they are stopped right in the middle of something data might become corrupted.

So, does stopping SQL Server Agent mean that jobs are 'killed', or is it just so that no more jobs will be scheduled to run from that moment?


texts: SSAS Hierachies duplicates when browsing

texts:
The dimensions in the cube are giving us duplicates, there is no error while processing the cube, but when the cube is browsed we could see duplicates.

There is a hierarchy in our dimension, the hierarchy is built like Country -- State -- Area.

In the DB, the dimension data looks something like below.

+---------+------------+-----------+
| Country |   State    |  Subarea  |
+---------+------------+-----------+
| India   | Karnataka  | Bangalore |
| India   | Telangana  | Hyderabad |
+---------+------------+-----------+

Now, when the cube is browsed with the attribute Country, we see 2 members under country as "India", when measure is selected against the attribute, the sales are shattered between these 2 values.

Can we do any work around to avoid this problem?


texts: Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables

texts:
I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned.

About the application: It's Nimsoft suite, so the SQL queries are not written by developers and comes with the suite.

OS : Centos 7
MySQL Version : 5.7.20 Community server
RAM : 120 GB
cores: 24

Looking at the ouput of cat /sys/block/sda/queue/rotational and lsblk I suspect the hard disks are not SSD but I'm not sure because all I can see is VMWare virtual disk from my OS. So I will cross verify with storage team.

Observations so far:

1) No CPU pressure at all. ~10% of the CPU is used.

2)Looking at the MySQL workload with show processlist, I can see one thread is connected and executing continuously (processID is same and never goes to sleep).

3) Connected thread is only executing SQL

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_098
    FILEDS TERMINATED BY ',' LINES

But I can see the Loading in table happens fast because immediately when I execute show processlist again I can see Loading happens in different table

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_080
    FILEDS TERMINATED BY ',' LINES

Each these tables are InnoDB tables created with hundreds of partitions.

4) I enabled Slow query log to capture any long running queries and I couldn't see any select query appearing in slow log for a long_query_time value of 1s.

5)Looking at the iostat output , I don't see abnormal qsize or waitime for write operations.

6) innodb_buffer_pool is set to 40 GB but the top command shows mysqld is constantly consuming more than 90% memory.

Since it is a suite and not much control we have over the SQL that is executing and also there are no slow running selects to identify and optimize.

What are the settings at the server level/ mysql level can be done in this case to improve the overall performance?

All other info about this MySQL server , I have uploaded in pastebin

Globalvariables
my.cnf
IOSTAT
Globalstatus
Filesystem
ulimit
top
mysqltuner


texts: Problem With Loading txt File in mySql 5.6 Command Line Client

texts:
I have run this to enable local infile:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

And indeed after that the local infile in on, but when I try loading file again, I still get notification, saying that my file does not exist.

Can someone tell me what should be done ?


texts: Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

texts:
Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary key in the result (using a UNION). What is the most efficient way to return the entity_id's? I am assuming a simple CAST would do the trick, but this is to handle a sitewide search which requires it to be as quick as possible.

    select entityA.a_id::TEXT as entity_id from entityA
    union
    select entityB.b_uuid::TEXT as entity_id from entityB
    union
    select entityC.c_id::TEXT as entity_id from entityC
    union
    select entityD.d_id::TEXT as entity_id from entityD
    union
    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?


texts: Is it necessary for a foreign key to be a primary key in another table? Why?

texts:
I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table.
Foreign key is not unique - it may have duplicate entries. If it must be primary key of another table then why it is necessary?
I think any column can be a foreign key because you can put condition at any column.


texts: Getting proper output of my SELECT clause. SQLite

texts:
I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided.

I have created the STOP table

CREATE TABLE STOP (
ID INTEGER PRIMARY KEY,
STOP_NAME TEXT NOT NULL,
ROUTE_ID INTEGER NOT NULL
);

and the TIMETABLE table

CREATE TABLE TIMETABLE (
ID INTEGER PRIMARY KEY,
TIME TEXT NOT NULL,
STOP_ID INTEGER NOT NULL,
FOREIGN KEY (STOP_ID) REFERENCES STOP (ID)
);

The route starts in Lidzbark Warmiński and leads via Smolajny and Dobre Miasto to Olsztyn. There are four connections per day. And I have also inserted all the possible routes from Lidzbark Warmiński to Olsztyn

INSERT INTO STOP VALUES
(1,'Lidzbark Warmiński', 1),
(2, 'Smolajny', 1),
(3, 'Dobre Miasto', 1),
(4, 'Olsztyn', 1),
(5, 'Lidzbark Warmiński', 2),
(6, 'Smolajny', 2),
(7, 'Dobre Miasto', 2),
(8, 'Lidzbark Warmiński', 3),
(9, 'Smolajny', 3),
(10, 'Smolajny', 4),
(11, 'Dobre Miasto', 4),
(12, 'Smolajny', 5),
(13, 'Olsztyn', 5),
(14, 'Dobre Miasto', 6),
(15, 'Olsztyn', 6),
(16, 'Smolajny', 7),
(17, 'Dobre Miasto', 7),
(18, 'Olsztyn', 7);

and the timetables regarding them

INSERT INTO TIMETABLE VALUES
--towards Olsztyn
(1, '08:00', 1),
(2, '12:00', 1),
(3, '15:00', 1),
(4, '19:00', 1),
(5, '08:00', 2),
(6, '12:00', 2),
(7, '15:00', 2),
(8, '19:00', 2),
(9, '08:00', 3),
(10, '12:00', 3),
(11, '15:00', 3),
(12, '19:00', 3),
(13, '08:20', 4),
(14, '12:20', 4),
(15, '15:20', 4),
(16, '19:20', 4),
(17, '08:20', 5),
(18, '12:20', 5),
(19, '15:20', 5),
(20, '19:20', 5),
(21, '08:30', 6),
(22, '12:30', 6),
(23, '15:30', 6),
(24, '19:30', 6),
(25, '08:20', 7),
(26, '12:20', 7),
(27, '15:20', 7),
(28, '19:20', 7);

I am developing under python 3.8 and this is a function with my SELECT clause

def fetch_hours(cursor, stop_from_name, stop_to_name):
    cursor.execute(
        '''SELECT T.TIME FROM TIMETABLE T
             JOIN STOP S1 ON T.STOP_ID = S1.ID
             JOIN STOP S2 ON S2.ROUTE_ID = S1.ROUTE_ID
             WHERE S1.STOP_NAME = ?
             AND S2.STOP_NAME = ? ORDER BY T.TIME ASC''',
        (stop_from_name, stop_to_name))
    return cursor.fetchall()

However, I have encountered some problems. For a wide range of bus stops this code doesn't work well. Providing stop_from Smolajny and stop_to Dobre Miasto, the result is

Departures: 08:00, 08:30, 12:00, 12:30, 15:00, 15:30, 19:00, 19:30

While I would like to see only four hours. For some connections it doesn't work at all and for some it is OK.

The desired effect is to see four departure hours for any stop_from and stop_to on this route (from Lidzbark Warmiński to Olsztyn). The reverse route is now commented, and if it would be helpful, the rest of my code can be achieved here: https://github.com/wwmarkositrawe/autobusy-backend

While looking for help,
Wishing you best regards,
marasecki


texts: T-SQL query must include and exclude child rows

texts:
I have simple parent -> child (one to many) relationship. Need to return children that have all given attributes, in this case red hair and brown eyes.
Table:
CREATE TABLE [dbo].[children](
    [child_id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NOT NULL,
    [attribute] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_children] PRIMARY KEY CLUSTERED
(
    [child_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Data:
insert into children (parent_id, attribute) values (1, 'red hair');
insert into children (parent_id, attribute) values (1, 'blue eyes');
insert into children (parent_id, attribute) values (2, 'red hair');
insert into children (parent_id, attribute) values (2, 'brown eyes');
insert into children (parent_id, attribute) values (3, 'purple hair');
insert into children (parent_id, attribute) values (3, 'brown eyes');

Desired Result:
parent_id
2

Semi Working Query:
select parent_id

from children

where attribute in ('red hair','brown eyes')

group by parent_id

having count(child_id) = 2;

That query works, however I would think there is a better way, (maybe not?) where I don't have to provide a count of the given arguments.
Update: Previous question and example were overly complicated and not communicated well. Hopefully this simplifies the issue.


texts: mysql workbench failed to connect at localhost:3306 with user root ssl connection error: ssl is required but the server doesn't support it

texts:
Not sure what the answers are, but I will say that I have not found the answers on how to make this work again.

The keyword support makes me think that a payment is required for this feature, but I'm not certain.


texts: Longitude and Latitude order with ST_X, and ST_Y on SRID 4326

texts:
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.

Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:

INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
  ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
  10,
  "Baptiste",
  " ",
  null,
  null,
  null
);

  SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

SELECT
   *,

    ST_Y(loc) as lat,
    ST_X(loc) as lng

FROM
  organization;

What am I not understanding about how these functions and coordinates work?

Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???

TIA


texts:
Version Information:

Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
HP Server Automation v9.14

I am creating a report of all my HP-SA managed servers.   The report includes data from multiple tables.   With the exception of Networking information everything is included on one row.    I'm trying to come up with a method to concatenate the NIC NAME, MAC, IP, DNS so that I don't get multiple rows for each active NIC.

I ran into roadblocks with sub select queries not knowing how to pass the D.DVC_ID from the outer query into the sub query.     I briefly tried using WM_CONCAT and LISTAGG without success.      Any help would be greatly appreciated

Posting the full SQL in case any other HPSA users out there could benefit from knowing where in the database to look for some basic hardware information:

SELECT D.SYSTEM_NAME AS "HOSTNAME",
    D.DVC_DESC AS "OPSWARE NAME",
    I.HW_ADDR AS "MAC ADDRESS",
    I.SLOT AS "INTERFACE",
    I.IP_ADDRESS AS "IP ADDRESS",
    I.NETMASK AS "SUBNET",
    D.DEFAULT_GW AS "GATEWAY",
    D.DVC_MFG AS "SERVER VENDOR",
    D.DVC_MODEL AS "SERVER MODEL",
    CONCAT (
        ROUND(MEM.QUANTITY / 1024 / 1024, 1),
        ' GB'
        ) AS "RAM",
    CPU.CPU_MODEL AS "CPU TYPE",
    COUNT(CPU.CPU_SLOT) AS "CPU QUANTITY"
FROM TRUTH.DEVICES D
INNER JOIN TRUTH.MEMORY_COMPONENTS MEM ON (D.DVC_ID = MEM.DVC_ID)
INNER JOIN TRUTH.CPU_COMPONENTS CPU ON (D.DVC_ID = CPU.DVC_ID)
INNER JOIN TRUTH.DEVICE_ROLES DR ON (D.DVC_ID = DR.DVC_ID)
INNER JOIN TRUTH.INTERFACES I ON (D.DVC_ID = I.DVC_ID)
INNER JOIN TRUTH.CUSTOMER_CLOUDS CC ON (DR.CUST_CLD_ID = CC.CUST_CLD_ID)
INNER JOIN TRUTH.DATA_CENTERS DC ON (DC.DATA_CENTER_ID = CC.DATA_CENTER_ID)
WHERE MEM.MEMORY_TYPE != 'SWAP'
    AND I.ENABLED_FLG = 'Y'
GROUP BY D.SYSTEM_NAME,
    D.DVC_DESC,
    I.HW_ADDR,
    I.SLOT,
    I.IP_ADDRESS,
    D.DVC_MFG,
    D.DVC_MODEL,
    MEM.QUANTITY,
    CPU.CPU_MODEL,
    I.NETMASK,
    D.DEFAULT_GW
ORDER BY HOSTNAME ASC,
    INTERFACE ASC

With a sample result of:

HOSTNAME         OPSWARE NAME  MAC ADDRESS        INTERFACE                IP ADDRESS      SUBNET         GATEWAY       SERVER VENDOR  SERVER MODEL             RAM   CPU TYPE                                CPU QUANTITY
---------------  ------------  -----------------  -----------------------  --------------  -------------  ------------  -------------  -----------------------  ----  --------------------------------------  ------------
WIN-6V18DDNQRL4  Server1       00:50:56:BB:66:A2  Local Area Connection    172.31.0.149    255.255.255.0  172.31.0.10   VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2
WIN-6V18DDNQRL4  Server1       00:50:56:BB:03:D3  Local Area Connection 2  169.254.246.97  255.255.0.0    172.31.0.10   VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2

Desired Output would have everything to do with the server on one row similar to the following:

HOSTNAME         OPSWARE NAME  (MAC ADDRESS/INTERFACE/IP ADDRESS/SUBNET/GATEWAY)                                                                                SERVER VENDOR  SERVER MODEL             RAM   CPU TYPE                                CPU QUANTITY
---------------  ------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------------------  ----  --------------------------------------  ------------
WIN-6V18DDNQRL4  Server1       (00:50:56:BB:66:A2/Local Area Connection/172.31.0.149/255.255.255.0/172.31.0.10,00:50:56:BB:03:D3/Local Area Connection 2/169.254.246.97/255.255.0.0/172.31.0.10)  VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2

Thank you,
Lars


texts:
We have a MEAN application with a 3-member MongoDB replica set. Somehow the total connection count keeps increasing until the primary is not able to take any new connections and goes down, making the application painfully slow. I have set the ulimit to 30000 and restarted the MongoDB and in no time it got 28000+ connection opened and primary went down.

Any idea why this may be happening? Most of the connections are between the different ports on primary with port 27017 on primary (for instance,  primary_server:45002 to primary_server:27017).


texts: Alternate of TimeStamp for row versioning and Peer to Peer Replication

texts:
Scenario: As per our findings the table cannot be used in peer to peer replication if it contains column with datatype timestamp.

MSDN:https://msdn.microsoft.com/en-us/library/ms151196(v=sql.105).aspx

Err:

We are using timestamp columns for row versioning in almost every table that need to be replicated.

Question:
For peer to peer replication case, is there any alternate of timestamp column  ?

Note: Rowversion datatype reacts same as timestamp.


texts: Representing ownership of heirarchical tree structure

texts:
In an application I'm developing, I have (among others) two structures: Users and Folders.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE folders (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES folders (id),
    name TEXT NOT NULL
);

My problem is that I want each folder tree to have exactly one user "owner", and preferably, the owner would be the same for all folders descending from a particular root node (i.e. WHERE parent_id IS NULL). I would like to maintain referential integrity in the database.

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems... a bit gross.)

Is there some way to do this that I'm not thinking of? I am open to using a different tree structure than an adjacency list, but I'm not sure that others have any additional benefit in this case.

Note: This is a simplified version of my actual use case, but the design is the problem, not the specifics. Similarly, I am using PostgreSQL, if that matters, but I doubt that it will...


texts: Improving performance for sum and group by with large datasets

texts:
I have a query that looked like this:

SELECT sum(score) as score, count(distinct(user_id)) number, round(sum(score)/count(distinct(user_id))) score_per_member, teams.*, types.name as type
FROM `teams` inner join `users` on `teams`.`id` = `users`.`team_id`
inner join `results` on `results`.`user_id` = `users`.`id`
inner join `types` on `types`.`id` = `teams`.`type_id`
WHERE `results`.`year` = '2015'
GROUP BY `team_id`
ORDER BY `score` DESC;

Users has 250k rows. Results has 4m rows with 1m with year 2015.

Although explain doesn't look too bad:

1   SIMPLE  teams   ALL PRIMARY,teams_type_id_foreign   NULL    NULL    NULL    4449    Using temporary; Using filesort
1   SIMPLE  types   eq_ref  PRIMARY PRIMARY 4   teams.type_id   1
1   SIMPLE  users   ref PRIMARY,users_team_id_foreign   users_team_id_foreign   5   teams.id    57  Using where; Using index
1   SIMPLE  results ref user_id,results_year_user_id_index  results_year_user_id_index  6   const,users.id  6

It was taking 50 seconds to run.

I refactored it to:

SELECT score, number, round(score/number) score_per_member, teams.*, types.name as type FROM `teams`
    inner join
        (
            select sum(score) score, team_id, count(distinct(user_id)) number
            from `results`
            join users on `results`.`user_id` = `users`.`id`
            WHERE `year` = '2015' and year_id is not null
            GROUP BY `team_id`
    ) r on teams.id = r.team_id
    inner join `types` on `types`.`id` = `teams`.`type_id` ORDER BY `score` DESC;

Explain actually 'looks' much worse:

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2045    Using filesort
1   PRIMARY teams   eq_ref  PRIMARY,teams_type_id_foreign   PRIMARY 4   r.team_id   1
1   PRIMARY types   eq_ref  PRIMARY PRIMARY 4   teams.type_id   1
2   DERIVED users   range   PRIMARY,users_team_id_foreign   users_team_id_foreign   5   NULL    132496  Using where; Using index
2   DERIVED results ref user_id,results_year_user_id_index  results_year_user_id_index  6   users.id    6

But the performance is now much, much better (2 seconds).

How could I improve queries like this even more? Is it just a case of throwing more hardware at it?


texts: How to find a session with a specific command

texts: I have an Oracle instance with some active sessions from different users. How can I find the SID/SERIAL of a specific session given that this session is surely running a specific SELECT I know?


texts: Copy a database to a new schema in an existing database

texts:
Just curious if there is a way to create a new schema that is based on an existing database.
Assuming the existing database has dbo schema only.
I will be taking an entire database and moving it to an existing database as a new schema within that database.


texts: How to get multiple "last values" for different criteria from a single column?

texts:
Our interest_log table stores events of users expressing interest on foo.

CREATE TABLE interest_log (
    id bigint NOT NULL PRIMARY KEY,

    user_id bigint REFERENCES foo_user(id),
    foo_id bigint NOT NULL REFERENCES foo(id),

    action varchar(255) NOT NULL,
    comment varchar(300) -- nullable
);

Some of the available actions are:

VIEW, FAVOR, UNFAVOR, DISMISS, and UNDISMISS.

I want to create a materialized view that provides the latest state of whether a user favors or dismisses a foo.

CREATE MATERIALIZED VIEW interest
AS
SELECT
    il.user_id,
    il.foo_id,
    ??? AS is_dismissed,
    ??? AS is_favored
FROM
    interest_log AS il,
WHERE
    ???

I came up with this query to select is_dismissed - but it feels too complicated and I don't see how I can combine it with fetching the latest favored state as well.

SELECT
  il.action = 'DISMISS' AS is_dismissed
FROM interest_log AS il
WHERE
  il.action IN ('DISMISS', 'UNDISMISS')
  AND NOT EXISTS (
    SELECT 1
    FROM interest_log AS il2
    WHERE
      il.action IN ('DISMISS', 'UNDISMISS')
      AND il2.id < il.id

What could the materialized view query look like and what fields/indexes should we add to make refreshs fast?

postgres:10.11


texts: Does the week day number changes according to localization in SQL Server?

texts: Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?


texts: What's the overhead of rarely used columns

texts:
We have a table with a lot of columns that are rarely used (most are NULL).

Can this slow down queries?
More importantly, do the empty columns take a lot of space per row?


texts:
I am working on a SaaS application using AWS MySQL.
The application is for multiple organizations. We use the same database and tables to store multiple org data. We store org_id in most of the tables and use this in select, update and delete queries as where org_id = ? .
DB Model

Organization
org_id

User
user_id
org_id

client
client_id
org_id

client_contact
client_id
phone_number
phone_type

project
project_id
org_id

user_project
project_id
user_id

The data is growing rapidly and we need to shard the data. We have customers from all over the world.
If I use a different sharding id for different table, then a join might happen across the nodes. For example, for user, sharding can be on shard_id and for client, sharding can be on client_id and for project, sharding can be on project_id. Here, the problem would be joining project and user. The user_id and project_id might be on different nodes and join will happen across nodes.
What is the best approach here? I am thinking of sharding based on org_id as I store org_id in most of the tables.
I see two problems here:

A few child tables don't store org_id as the parent table is storing. Do I need to store the org_id in all the tables?
Some orgs might have more data and load which might lead to a hot spot and more storage on a particular node. Is it possible scale a particular node alone with AWS RDS?

Please suggest the best approach.
Note Each org might have up to 500,000 records in any table with around 10 columns. We could reach more than 5,000 organizations in 6 months. 500,000 multiplied by 5,000 orgs will reach 2.5 billion records.


texts: Intarray is available but can't seem to be able to use the icount function

texts:
My question is below. Following the suggestion from one of the comments, I started to search for search_path and extensions. I came on to this post: https://stackoverflow.com/questions/12986368/installing-postgresql-extension-to-all-schemas. The answer by a_horse_with_no_name worked brilliantly! Thank you!! (And, thank you to Krismorte for the edits making the post much more readable).

I am running PostgreSQL 10.

I ran the following command to set it up:

sudo su - postgres -c psql template1 <<OMG
DROP DATABASE IF EXISTS test;
DROP USER IF EXISTS "ec2-user";
CREATE USER "ec2-user" WITH PASSWORD '*******';
ALTER USER "ec2-user" CREATEDB;
CREATE EXTENSION intarray;
OMG

I then created another DB, sent_4_snps using the ec2-user. This was done within a Python script using the following code:

sys.stdout.write('The SNPdb {0} does not exist - running sql to create database\n'.format(self.snpdb_name))
make_db_conn_string = 'host=\'{0}\' dbname=postgres user=\'{1}\' password=\'{2}\''.format(self.pg_host,
                                                                              self.pg_uname, self.pg_pword)
conn = psycopg2.connect(make_db_conn_string)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute('CREATE DATABASE {0}'.format(self.snpdb_name))
cur.close()
conn_string = 'host=\'{0}\' dbname={1} user=\'{2}\' password=\'{3}\''.format(self.pg_host, self.snpdb_name,
                                                                             self.pg_uname, self.pg_pword)
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
sql_script = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'template_snapperdb_denovo_refs_sql')
cur.execute(open(sql_script, 'r').read())

This essentially runs the CREATE DATABASE command as the ec2-user.

The DB has the following table:

Table "public.strains_snps"

   Column    |       Type        |
-------------+-------------------+
 name        | character varying |
 variants_id | integer[]         |
 ignored_pos | integer[]         |
 id          | integer           |

If I try to run the following query:

select variants_id, name, icount(variants_id) as count
from strains_snps;

I get the following error:

  ERROR:  function icount(integer[]) does not exist LINE 1: select
  variants_id, name, icount(variants_id) as count from ...
                                    ^ HINT:  No function matches the given name and argument types. You might need to add explicit type
  casts.

I have tried re-running the command to add the extension, but I just get a message saying that the extension has already been created. I have searched everywhere, and I can't seem to find any other information. I have tried using \df on the psql to search for the icount function, but it doesn't find it.

Any help would be greatly appreciated.


texts:
I would like to do a search across columns of joined tables. What would the best index configuration be for the query below?
Table A
------------
Id
ValueA
-- Table A has many more columns

Table B
------------
Id
AId
ValueB
-- Table B has many more columns

At the moment, I have clustered indexes on the Primary Keys and non-clustered indexes (which don't include any columns) for columns AId, ValueA, and ValueB.
SELECT *
FROM TableA a
INNER JOIN TableB b ON a.Id = b.AId
WHERE
    a.ValueA LIKE 'SearchTerm%' OR
    b.ValueB LIKE 'SearchTerm%'

Update
Table A is likely to run into the low millions while Table B will only have low tens of thousands of entries.
In the real world Table A will be a list of transactions, where the value is some sort of a transaction reference. Table B will be a list of users where the value could be anything such as a name or any other descriptive information.
At the moment we use an ORM which will essentially do a SELECT *. Naturally this could be changed, but there would be a fairly large impact on the application side.


texts: Abbreviated I have following tables: Companies own flats and rent them for specific periods_in_time and lastly a currencies table. The price column is sitting in the periods_in_time table. But where should my currency.id column go? My idea was to put it inside flats. As a company could own flats in different countries and needs to rent them by local prices. Am I correct? Other ideas?


texts:
I would like to run a query very similar to this on one of my production databases:

-- Setup the table (Already there in my real scenario)
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
insert into dbo.MyTable values
(56,'I'), (12,'I'), (56, 'II'), (12, 'II'), (56, 'III'), (56, 'IV'), (56, 'V'), (12, 'V')

-- Create a function to cross apply with (I can't make this in my production env)
create function dbo.GetOrderGroup(@groupName varchar(15))
returns @ReturnTable table(GroupValue varchar(8000), GroupName varchar(15))
as
begin
    DECLARE @groupValue VARCHAR(8000)

    SELECT @groupValue = COALESCE(@groupValue + ', '
           + cast(MyValue as VARCHAR(10)), cast(MyValue as VARCHAR(10)))
    FROM MyTable
    where MyGroup = @groupName

    insert into @ReturnTable(GroupValue, GroupName)
    SELECT @groupValue, @groupName
    return;
end;
go

-- cross apply and get the distinct values
SELECT  grp.GroupValue, count(distinct (grp.GroupValue + grp.GroupName)) as 'Count'
from    MyTable tbl (NOLOCK)
        cross apply dbo.GetOrderGroup(tbl.MyGroup) grp
GROUP BY grp.GroupValue

But I don't have rights to create a User Defined Function on that database (I am a developer).

Is there a way to accomplish this query with out the use of a user defined function (or any other "Create" statement)?

Note: I tried doing a cross apply with the contents of my Function, but it failed (I don't think multi-statement expressions are allowed in the Cross Apply).