texts: AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?

texts:
I have a Multi-AZ AWS RDS instance running MYSQL 5.5

I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec over last 12 months and last 2 weeks, and Avg DB connections over last 2 weeks:

I know the overall level of write ops/sec is low in the scheme of things, but I'm expecting more volume (like 100x) over the next few months and want to make sure I'm on top of any issues.

I'm trying to understand what is causing these write ops. I've tried connecting to the RDS instance and executing:

show full process list

and this shows:

+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
| Id     | User     | Host                                              | db    | Command | Time | State | Info             |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+
|      7 | rdsadmin | localhost:51067                                   | mysql | Sleep   |    9 |       | NULL             |
| 705938 | rdsuser  | ip-xx-xx-xx-xx.eu-west-1.compute.internal:xxxx | NULL  | Query   |    0 | NULL  | show processlist |
+--------+----------+---------------------------------------------------+-------+---------+------+-------+------------------+

ie no active threads.

Appreciate any thoughts on what's going on.

UPDATE
Thanks to Roland's response, I did some further digging. In particular, I adding general logging and slow query logging to the RDS instance and saw that a cron job I had running every minute was making unnecessary queries that was making the instance do quite a lot of un-needed work.

I switched on logging by creating a new Parameter Group in RDS Management console and setting:

general_log = 1
slow_query_log = 1
long_query_time = 1

Note that I also used

CALL mysql.rds_rotate_slow_log;

and

CALL mysql.rds_rotate_general_log;

to rotate the log files - as they get pretty large pretty quickly.


texts:
I have a list of oracle dbf files and I need to restore the information inside these files, there are no control files and anything else. only DBF file
http://s9.postimg.org/mxmx0g73j/Untitled.jpg

Is there any parsing tool or recovery tool that can open DBF files and unload the information in it..


texts: Bulk insert and fast ordered search large data 3B+ records

texts:
Scenario

We want to study behavior of some similar entities that changes based on time.

Currently I'm using MS SQL Server 2016

Table Structure

CREATE TABLE [dbo].[BehaviorHistory](
    [EntityId] [uniqueidentifier] NOT NULL,
    [BehaveFactor] [bigint] NOT NULL,
    [TimeOffset] [bigint] NOT NULL,
 CONSTRAINT [PK_BehaviorHistory] PRIMARY KEY CLUSTERED
(
    [EntityId] ASC,
    [BehaveFactor] ASC,
    [TimeOffset] ASC
)

Sample Query

We want to see this similar entities how often repeat same behavior as our test subject

SELECT *
FROM
    (
        SELECT
        EntityId,
        BehaveFactor,
        (TmpOffset.PerviousMainTimeOffset - TmpOffset.CurrentMainTimeOffset) MainDeltaTime ,
        (TmpOffset.PerviousSubTimeOffset - TmpOffset.CurrentSubTimeOffset) SubDeltaTime
        FROM
            (
                SELECT
                LAG(main.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousMainTimeOffset,
                LAG(testSub.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousSubTimeOffset,
                main.EntityId AS EntityId,
                main.BehaveFactor as BehaveFactor,
                main.TimeOffset AS CurrentMainTimeOffset,
                testSub.TimeOffset AS CurrentSubTimeOffset
                FROM BehaviorHistory AS main
                INNER JOIN SampleBehaviorHistory AS testSub ON main.BehaveFactor = testSub.BehaveFactor
            ) as TmpOffset
    )AS MatchTable

Where
MainDeltaTime = SubDeltaTime --repeated behavior in same time order
AND MainDeltaTime <> 0 --exclude repeated behavior at the same time

Problem

We currently inserting large amount of data into BehaviorHistory table
its size is like 100 GB more with more than 3 Billions records in last 8 months or so.

The sample query performance is good (like 300ms) if we have index but when we create non clustered index on BehaveFactor (includes TimeOffset) the insert operation timeout on this table, so currently I have two DB one without index used for inserting data and another that every day get copy of inserted DB and create index on it and used for reports.

Error

With no index its works fine, when we try to insert in a indexed table after a couples of bulk insert (eg 2000 records) we got this error

[fnInsertBehaviors(EntityId : 1705438)]
#     1 : The underlying provider failed on Open.
#     2 : Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=58416; handshake=9;
#     3 : The wait operation timed out
#

Looking for solution to provide real time reports (not based on yesterday data) and avoid two DB and scheduled backup restore to create indexed table.

~600 records inserted per second in a certain period of time in a day.

With non clustered index, insert query timeout and without it performance is out of question. Insert fails in normal mode with no lock on table, normal insert fails when we have no query running against that table.

Also I need to consider growing database maybe next year its size is 300 GB so I have to find a solution that can handle that much data before it gets too late and system fails.


texts: Connecting to SQL Server instance through C#

texts:
I made a program, using c# and sql server with sql authentication. It works properly in my device, but after making the program setup file .exe extension using Innosetup, I cannot connect to my database.

I've have tried exporting my database in script, and run the script on another device but it does not work. I think I need to create port or something to connect to this database, or in my app.config change the server name

Here is my Connection String

<connectionStrings>
    <add name="MarketDatabaseEntities" connectionString="metadata=res://*/AmanjCenter2Entity.csdl|res://*/AmanjCenter2Entity.ssdl|res://*/AmanjCenter2Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=RABAR;initial catalog=MarketDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>


texts: How do I keep a postgres table up to date using instagrams api?

texts:
I want to update a table in postgres with the response I get from the instagram api.

I saved the response as a json file and imported it using navicat but when I saved the import job in navicat it ends up as a nppi file. In the nppi file I can change the source to the http address but I cant use that import file in pgadmin.

How do I make this automatic? I could do it if the import file was in sql but it's as not. Am I missing something that could be easier? In pgAdmin the import wizard doesn't have json as an option. I'm still using navicat's free trial so I don't want to be stuck with it because of this.

Is there an easy way to get a postgres table to update with the instagram api?


texts:
I have mysql cluster with two datanodes and noofreplicas=2. According to documentation there must be 1 nodegroup, 2 partitions and 4 parts at all (2 primary and 2 backup).
I have a table with 1000000 rows. I want to know how they distributed.

[root@localhost ~]# ndb_desc users -d test_1 -np
-- users --
Version: 10
Fragment type: HashMapPartition
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 289
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
HashMap: DEFAULT-HASHMAP-3840-4
-- Attributes --
user_id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
user_data Longvarchar(1024;latin1_swedish_ci) NULL AT=MEDIUM_VAR ST=MEMORY
-- Indexes --
PRIMARY KEY(user_id) - UniqueHashIndex
PRIMARY(user_id) - OrderedIndex
-- Per partition info --
Partition   Row count   Commit count    Frag fixed memory   Frag varsized memory    Extent_space    Free extent_space   Nodes
0           249356      249356          8028160             11304960                0               0                   1,2
2           250249      250249          8060928             11337728                0               0                   1,2
1           250663      250663          8060928             11370496                0               0                   2,1
3           249732      249732          8028160             11304960                0               0                   2,1

NDBT_ProgramExit: 0 - OK

Why there are 4 partitions with ~250000 rows at each instead of 2 partitions with ~500000 rows?


texts: how to replace user's names with random names?

texts:
How can I use SQL only on mysql DB to replace first and last names of real users in users table using a random name from two related tables: random_first_names and random_last_names. Our users table contains over 250K records and each of the rundome tables contain over 5000 names that should be picked at random for each record in users table. Is it possible to achieve with SQL only?

[UPDATE]

I solved the problem based on Rick's input and shared the full SQL script below.


texts: Is there any way I can speed up this large full-table query?

texts:
I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 million rows out from a table of 13 million rows (the other 9 million records are older than 2019), and it is returning all of the columns, of which there are 101 (a mix of datetime, varchar, and int columns). It has two indexes, a clustered one on its primary key interaction_id, and an unclustered index on interaction_date which is a datetime column that is the main filter. This is the query:

  SELECT *
  FROM [Sales].[dbo].[Interaction]
  WHERE
  year(Interaction_date) >= 2019

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself? Before I go into an ETL processes or fight back on the group that needs this query (they are a Hadoop sqooping team who insist they need to sqoop all of these records all the time with all of the columns), I want to see if I can make it easier on people by doing something on my end as the DBA.

The query plan by default ignores my non-clustered index on the interaction_date column and still does a full clustered index scan. So I then tried forcing it to use it by including WITH (INDEX(IX_Interaction_Interaction_Date)) in the select.

This forces it into the query plan startign with an index scan of the non-clustered index, with estimated rows 4 million but estimated rows to be read as all 13 million. Then after a short time it spends the rest of the execution on the key lookup of the primary clustered index.

But ultimately, it doesn't seem to speed up the query at all.


texts: How to increase max_locks_per_transaction in MacOSX

texts:
I've been performing kind of intensive schema dropping and creating over a PostgreSQL server,

  ERROR: out of shared memory

  HINT: You might need to increase max_locks_per_transaction.

I need to increase max_locks_per_transaction but how can i increase it in MAC OSX


texts:
I have a Dell 2900 Server with 16 GB of memory running SQL Server 2005.

All 3 of the databases I have configured on the Server all have the maximum memory allocated to each Database. The primary database runs my production environment of our BAAN application, the other two databases run a development environment.

Besides reducing the amount of memory used in the other 2 Databases, is there a command I can issue to show the memory used by each database?


texts: sys.objects column [Type] weird value 'ST'

texts:
I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table)

Does anyone know what this 'ST' value means? (This is in SQL Server 2014 Developer Edition)


texts: Creating view recursive query in Oracle DB results in ORA-00600 (ORA-600)

texts:
Hello Ladies and Gents,

I've been trying to deploy a recursive query as a view in Oracle XE and Standard Edition, neither to much success.

The query is located in this question here: https://stackoverflow.com/questions/17358109/how-to-retrieve-all-recursive-children-of-parent-row-in-oracle-sql

with recursion_view(base, parent_id, child_id, qty) as (
   -- first step, get rows to start with
   select
     parent_id base,
     parent_id,
     child_id,
     qty
  from
    md_boms

  union all

  -- subsequent steps
  select
    -- retain base value from previous level
    previous_level.base,
    -- get information from current level
    current_level.parent_id,
    current_level.child_id,
    -- accumulate sum
    (previous_level.qty + current_level.qty) as qty
  from
    recursion_view previous_level,
    md_boms        current_level
  where
    current_level.parent_id = previous_level.child_id
)
select
  base, parent_id, child_id, qty
from
  recursion_view
order by
  base, parent_id, child_id

The query itself works and returns results.  However, when I try to create a view with that query, I receive errors.

I've posted two screenshots: http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/ViewError.jpg and http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/InternalError.jpg The first one with the ! and * is when I am using JDeveloper 11g, using the Database Navigator, right-click on View and select "New View". I receive the error when I click OK. I can indeed create the view through a sql script, but when I attempt to view the data, I receive the ORA_00600 error.

I've tried this on both my local machine for XE (version 11.2.0.2.0) and on 11g Standard Edition (11.2.0.2.v6) via Amazon Web Services.  The result is the same.

Does anyone know how to get around this?


texts: Error on Inner Join from Nested Select Statements

texts:
I am trying to flatten an EAV style table. But I need to add a couple of columns from other tables. I read online that doing nested selects might be a good approach, though when I am getting an error on the last 2 joins (m6.meta_value and m7.meta_value)
"Multi-part identifier could not be bound". I am using SQL Server 2016.

Does anyone know how I can still get my joins and work around this error?

SELECT distinct patients.ID ,
(SELECT meta_value FROM USERMETA m1 WHERE patients.ID = m1.USER_ID AND m1.meta_key = 'first') AS FirstName,
(SELECT meta_value FROM USERMETA m2 WHERE PATIENTS.ID = m2.USER_ID AND m2.meta_key = 'last') AS LastName,
(SELECT distinct meta_value FROM USERMETA m3 WHERE PATIENTS.ID = m3.USER_ID AND m3.meta_key = 'customer') AS CustomerID,

(SELECT meta_value FROM USERMETA m6 WHERE PATIENTS.ID = m6.USER_ID AND m6.meta_key = 'clinic') AS Clinic,
isnull(clinics.name, 'No Clinic') as 'Name',
(SELECT meta_value FROM USERMETA m7 WHERE PATIENTS.ID = m7.USER_ID AND m7.meta_key = 'doctorsid') AS DoctorID

from users patients
INNER JOIN user_group ON user_group.user_id = patients.ID AND user_group.group_id != 22 AND user_group.group_id = 5
INNER JOIN revision ON patients.ID = revision.wp_users_id AND revision.revision_types = 2
INNER JOIN doctors doc ON m7.meta_value = doc.bt_id  --"m7.meta_value" has error
INNER JOIN fronts ON m6.meta_value = fronts.id       --"m6.meta_value" has error

group by patients.id
order by patients.ID


texts:
I have this query :

select p.id from product p
            join product_variant pv on pv.product_id = p.id
            JOIN channel_pricing pr on pr.product_variant_id = pv.id
            WHERE p.id in (1,2,3,4,5) and p.main_taxon_id = 2
            GROUP BY pv.product_id
            ORDER BY pr.price ASC

I'm getting this error :

  Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'sidratsoft_flora_db.pr.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Is there a way to fix it without changing the sql_mode?


texts:
I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project.

A User is associated with Message/Thread/Project as either the Creator or as it being 'shared' with them.

A Message is contained within a Thread (associated by message.thread_id and thread.id), and a Thread is contained within a Project (associated by thread.project_id and project_id).

I would like to create a query where given a User.id value, it will return all messages that the user has access to, as well as the Thread and Project name that that message is under, both as Creator or 'Shared'. I use a table to handle the 'shares'. The rough diagram is: http://min.us/mvpqbAU

There are more columns in each, but I left them out for simplicity.


texts: Postgres cannot be restarted after disk ran out of space

texts:
My HD ran out of space while a postgres was running. I had to kill postgres and freed some space. Now I am not able to restart postgres with the following error message:

LOG:  listening on IPv4 address "0.0.0.0", port 5434
LOG:  listening on IPv6 address "::", port 5434
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5434"
LOG:  database system was interrupted; last known up at 2018-04-16 05:20:46 EDT
PANIC:  could not read file "pg_logical/replorigin_checkpoint": Success
LOG:  startup process (PID 97490) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

In data/pg_logical there exists a file with the name replorigin_checkpoint but it is empty.

I already copied the data directory for backup reasons as suggested here but I am actually not really sure what to do next. amcheck looks like, it is only working on a running postgres.

./postgres -V
postgres (PostgreSQL) 11devel

Ubuntu 16.04.4 LTS

Maybe it is worth noting, that the file system is itself mounted on that machine (nfs)

Questions
1. What is supposed to be inside replorigin_checkpoint?
2. Is there the possibility to restart from an earlier checkpoint?
3. What type of corruption repairings are out ther (not only corruption detection)


texts: In the below Party Model diagram, why is there a COMMUNICATION EVENT WORK EFFORT entity, but no CASE WORK EFFORT derived from CASE entity? Should there be one single entity WORK EFFORT? Should it derive from COMMUNICATION EVENT or CASE entity or both? Is CASE required before WORK EFFORT?


texts:
We have an older SQL Server Database that is about 1TB.
We now have a new database that has a slightly different schema.  We are going to migrate the 1TB of data to the new db using the export tool in SSMS.
However to make it go faster, I am thinking maybe there is a way to disable unnecessary logging and backup during the migration?
We won't need to recover to a specific time state during the migration anyway and we have the original database.
Could I set the log size to something like 100MB initially, do the migration and then change it back to unlimited again?  Would it be viable to use SIMPLE recovery during the migration and then afterwards change it to FULL? I'm not sure what this tool uses to migrate data internally, probably bulk inserts.
Just for clarity, in SSMS, we will right click the database > Tasks > Export Data.  This allows you to map columns from the source database to the target database.  So, this is not a "restore" in that sense.  We need to copy from one db to another. The problem is that the new db has other column names and in some cases other datatypes. However this tool converts and truncates correctly.


texts: MSSQL Agent Task Failing - Stored Procedure

texts:
I have a stored procedure that is giving me some issues as an Agent Job

When running the task, I get the following error:

Executed as user: SQLUserName. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

The Task has 1 step and that step is just "EXEC dbo.Procdure_Name" and the "Run as" is blank (i'm unable to specify anything here). I have put in "Advanced", "Run as user" as SQLUserName.

When I remove this, I get the error:

Executed as user: domain\servename$. Login failed for user 'domain\servername$'. [SQLSTATE 28000] (Error 18456).  The step failed.

This is running a command on a linked server. When I remove that command it works.

The linked server is set to logon to "be made using the login's current security context" and testing the link server works fine.

If I log into SMSS as SQLUserName and run the procedure manually it works fine.

Would appreciate any suggestions.


texts: Set variable with dynamic name in trigger (MySQL)

texts:
I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this:

delimiter ;;

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    DECLARE columnName VARCHAR(20);
    SET columnName = 'my_column';
    SET NEW.{columnName} = 'my value'; -- pseudo-code
END;;

delimiter ;


texts:
Is there any advantages to use Third Party Tools to make Backups ?

I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL 2008R2, Standard and Enterprise Editions.


texts:
I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure:

CREATE PROCEDURE dbo.something
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS

    SELECT [IdNumber], [Code], [Status], [Sex],
           [FirstName], [LastName], [Profession],
           [BirthDate], [HireDate], [ActiveDirectoryUser]
    FROM Employee
    WHERE (@Status IS NULL OR [Status] = @Status)
    AND
    (
      @IsUserGotAnActiveDirectoryUser IS NULL
      OR
      (
        @IsUserGotAnActiveDirectoryUser IS NOT NULL AND
        (
          @IsUserGotAnActiveDirectoryUser = 1 AND ActiveDirectoryUser <> ''
        )
        OR
        (
          @IsUserGotAnActiveDirectoryUser = 0 AND ActiveDirectoryUser = ''
        )
      )
    )

And this is the index:

CREATE INDEX not_relevent ON dbo.Employee
(
    [Status] DESC,
    [ActiveDirectoryUser] ASC
)
INCLUDE (...all the other columns in the table...);

The plan:

Why did SQL Server choose a scan? How can I fix it?

Column definitions:

[Status] int NOT NULL
[ActiveDirectoryUser] VARCHAR(50) NOT NULL

Status parameters can be:

NULL: all status,
1: Status= 1 (Active employees)
2: Status = 2 (Inactive employees)

IsUserGotAnActiveDirectoryUser can be:

NULL: All employees
0: ActiveDirectoryUser is empty for that employee
1: ActiveDirectoryUser  got a valid value (not null and not empty)


texts:
I am new to database administration, and I need to implement auditing of all users of multiple databases.

Can we audit multiple users of a multiple database by creating just one audit specification?

I tried to create the audit specification, but I get the error message in the screenshot below.


texts: Why cannot login to restored SQL Server 2012 database?

texts:
As part of the effort for developing a Windows Service, I restored a production database to a test database on the same SQL Server instance, and can access the test database just fine via SSMS. I gave db_owner role to the database to two other users that are unable to login, both getting SQL error

  Error: 18456, Severity: 14, State: 38. Login valid but database
  unavailable (or login not permissioned)

Here is the basic message which mentions the database in question as the problem.

  Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open
  the explicitly specified database 'MedFile_TestDataServer'. [CLIENT: ]

The database is not in "Restoring" status.

First user is NT Authority\System and the second a Windows user. Both credentials are used to run the Windows service that access the database in update mode, the system user from the same server, the Windows user from VS2013 running the service as a command program on my desktop. Both can get at other copies of this same database just by changing the database name so don't think a connection string issue. I have compared every property on the databases that work and do not work and see no differences except the file names and these two logins have less permissions on the databases that they can access.

Almost like this database is being kept unavailable after being restore but I can find no such property set on the database and I can access via SSMS. I've restarted the server containing the database ergo SQL Server as well.

I also tried running the service as a database administrator and get the same error even though that user accesses the db just fine via SSMS.

Is there anything that can make the database "unavailable"?

This is part of software development for a Windows service trying to use a test database. I am using both EF 6.02 and the latest ADO.NET version as well. I've checked out all the normal setup and it is correct.

Thanks for any ideas, Dave


texts:
I am facing an issue in the shard distribution.

MongoDB Version: 3.0.15

I have a collection that is sharded. I have two shards.

Output of db.getCollection('event').stats()

{
 "sharded" : true,
 "capped" : false,
 "ns" : "coll.event",
 "count" : 386631514,
  size" : NumberLong(4178195857267),
 "storageSize" : 293516279808.0,
 "totalIndexSize" : 391203631104.0,
 "shards" : {
    **"shard0000" :** {
    "ns" : "coll.event",
    "count" : 182463940,
    "size" : NumberLong(2187685928765),
    "avgObjSize" : 11989,
    "storageSize" : 148825059328.0,
    "capped" : false,
    },
**"shard0001"** : {
    "ns" : "coll.event",
    "count" : 204167574,
    "size" : NumberLong(1990509928502),
    "avgObjSize" : 9749,
    "storageSize" : 144691220480.0,
    "capped" : false,
    }
},
"ok" : 1.0
}

Output of Shard Distribution

db.getCollection('event').getShardDistribution()

Shard shard0000 at localhost:27010

Even though there are two shards, the shard distribution shows only one. Now only one of the sharded folder is getting full.

Why is it so?
What do I have to do to make the shard distribution common?


texts: Set non-negative floor for rolling sum, in SQL Server

texts:
I need to set a floor on the rolling sum calculation. For example, with

PKID    NumValue    GroupID
----------------------------
1       -1          1
2       -2          1
3       5           1
4       -7          1
5       1           2

I would like to have:

PKID   RollingSum    GroupID
----------------------------- ## Explanation:
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum.

The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0

If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level.

The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

This business application is absolutely stupid, but there's nothing I can do about that. For this question, please only consider solution that can be used by a DBA.

I expect fifty rows per GroupID at most.


texts: Which columns should be indexed when all may be used in different search queries?

texts:
Background

I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, which means I have to have a column in certain tables which holds the ID of the city that each row belongs to.

Right now I have three different tables:

Cinemas - Contains each city's cinema (ID and name).
Movies - Contains all movies that has been/will be shown at the cinema.
Showtimes - Contains all showtimes for all movies in all cities.

The structure of the Showtimes table is the following:

Column Name   | Column Type  | Description
--------------+--------------+---------------
ID            | BIGINT       | (Primary) Unique ID for each showtime (perhaps unnecessary?)
CinemaID      | TINYINT      | Foreign key bound to Cinemas.ID
MovieID       | BIGINT       | Foreign key bound to Movies.ID
Showtime      | DATETIME     | At what date and time the movie will show

(will contain multiple rows for each movie, i.e. one row for each showtime)

How this table will be used

A user of the website must be able to:

View all current/upcoming movies and showtimes (sorted by date) in the selected city.

Example query (backend):

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? ORDER BY Showtime

Select a single movie and view all showtimes for that specific title only (in the selected city).

Example query:

SELECT Showtime FROM Showtimes WHERE CinemaID = ? AND MovieID = ? ORDER BY Showtime

Select a single day and view all movies and showtimes for that day only (in the selected city).

Example query:

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? AND (Showtime BETWEEN [date 12:00 AM] AND [date 12:00 PM])

So naturally I decided that I needed to create indexes for the columns.

Problem

What I'm having trouble with is deciding/determining how to index the columns properly. One index for each column seems quite expensive[1] [2] so I started looking into composite indexes, which seems to be the right choice but also led to even more confusion.

From my understanding (based on what I've read) you should add the columns to the index by order of selectivity, making the most selective (I'm guessing that means the most unique/with the most cardinality?) column the first in the composite index[3] (in my case that would be the Showtime column). The only problem with that is that the index can only be used by the database if the first column is included in the search query[4] [5], which it currently isn't in either of my queries.

Question

What kind of index(es) should I apply to my columns in order to cover all usage scenarios? (the last scenario may be omitted, but the first two are required)

Should I use a composite index on all columns, for some columns, or do I need a separate index for each column?

This table is updated at most a few times per week to add new showtimes.

Footnotes

1 MySQL indexes - what are the best practices?

2 Indexing every column in a table

3 How important is the order of columns in indexes? (question)

4 How important is the order of columns in indexes? (#2 top-voted answer)

5 When should I use a composite index?


texts: Automatic Failover or Database Mirroring?

texts:
We have a software backed with database. We want to keep database at a remote location and have it all the time available. For this task, a 5 year old project experience came into my mind. I remember, IT department over there asked me to deploy database not locally but remotely on another LAN computer. They told me, there will be two computers there but thanks to a special software, I will see only one Windows OS interface and that software will handle all sync, backup, replication .. tasks. They were calling it "virtual machine".

So with the IP they gave me, I just connected to so called virtual machine and I just deployed my SQL database there just like it is in a local machine. In summary, we need such a solution now for ourselves. I made some search on the net and I found out Automatic Failover, Database Mirroring etc. topics. But I am not sure these refer to what I described. At this point I need your help. Maybe there is a better solution after 5 year for such a task. I am not a database administrator.

I did not choose a specific database yet. Our software can connect to several number of major databases. Just seems that SQL will provide a robust solution for that. And In short, what we need is to have a database backend which will be always available to us. So  in the case of failures, it should have a system to automatically switch to working replica.


texts:
We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX callbacks.

The problem occurs on a page with various "fields" that contain information about a resource. The user can click on each field to turn it into "edit mode" which allows the change of data. Basically what this does is open a transaction, save the item, update the html and close the transaction.

The user can change the data and then save the changes again with a transaction. We also have buttons that can be pressed to trigger scripts (C# dynamic code) which run a new transaction.

Below is a small transcript of two threads running in such a scenario and what they're doing.

* Thread 1 *

Step 1. SET TRANSACTION ISOLATION LEVEL read committed
Step 2. begin transaction
Step 3. update Person set Name = 'Person1' where itemId  = 801
Step 7. update Person set Name = 'Person3' where itemId  = 801
Step x. commit transaction

* Thread 2 *

Step 4. SET TRANSACTION ISOLATION LEVEL read committed
Step 5. begin transaction
Step 6. update Person set Name = 'Person2' where itemId  = 801
Step x. commit transaction

After executing step 7 you will receive a deadlock.

> Transaction (Process ID 124) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

We're currently investigating how deadlocks are working in SQL Server and how we can prevent them from our side, but any input/advice on this to do/check is welcome.

If additional information is required, I can add it as long as it's not violating our rules here at work.


texts: Select rows from table where each row refers to a different schema

texts:
I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by customer 'a', I can do select * from a.portal.

I want to select all the customers having a particular 'portal name'. But portal table is inside customer schema. So how should I query in this situation?

I have found solution using a PHP script but is there anything that we can do on the DB level?


texts: Why can't I create a database in a 107GB dbspace I created for my instance?

texts:
11.70.FC6 Developer Edition on Windows 7 Pro 64-bit:

Downloaded via IIUG link and installed it.
I created a custom DSS instance named "server" with a 128GB dbspace named "datadbs".
Install wizard created a Windows user "informix".
I connected to instance "server", logging in as "informix".
Executed an SQL script "CREATE DATABASE voters IN datadbs NLSCASE INSENSITIVE;"
This failed (see first image), then tried creating the db via dbaccess menu.
In dbaccess, dbspaces options did not show "datadbs", only root, phys, log & sbs dbs',
but when I do a dir of the dbspaces it shows "datadbs" with 107GB (see second image).
I set the following values in "onconfig.server" (see third image), but still no dice!


texts:
Full error

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
InvalidArgumentException: There was a problem connecting to the database: SQLSTATE[HY000] [2019] Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)

mysql  Ver 14.14 Distrib 5.7.26

I'm trying to fix this error, but can't get it sorted on a live server. I have had to go through the procedure of adding the following to the /usr/share/mysql/charsets/Index.xml file on a local virtual machine which worked.

<charset name="utf8mb4">
  <family>Unicode</family>
  <description>UTF-8 MB4 Unicode</description>
  <collation name="utf8mb4_general_ci" id="45">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="utf8mb4_bin"     id="46">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

I've restarted mysql a couple of times since updating this to no avail. My database connection is listed like so

<?php
$db = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $db_username, $db_pass);

And all my database variable seem to check out based on other posts I've seen

my.cnf has the following set too

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Is there any kind of cache that could be failing to clear even after a service mysqld restart command? (CentOS 6)


texts: How can I order rows returned by the RETURNING clause of a DML statement?

texts:
I am using this statement to get all the rows ordered by timestamp in ascending order

DELETE FROM @tableName
        WHERE id = ANY (
            SELECT id
            FROM @tableName
            WHERE source = :p1 AND target = :p2 @readCondition
            ORDER BY createddate
            LIMIT @limit
            FOR UPDATE SKIP LOCKED
        )
        RETURNING *;

And I am getting these results :

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.230886"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.279604"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.276191"
"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.202338"

As you can see they are not sorted by createdate. Is there a way to sort rows from the RETURNING clause?


texts: How to safeguard a symmetric key in SQL Server

texts:
I've got a database in which I need to store the encrypted values for a column. I use the SQL Server encryption format for encryption. The format is as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'dsagfdsagv418515adsf'

CREATE CERTIFICATE 'CERTIFICATE_NAME'

CREATE SYMMETRIC KEY 'KEY_NAME'

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE 'CERTIFICATE_NAME';

Now I am able to encrypt and decrypt the column, using EncryptByKey and DecryptByKey functions respectively.

Now I see a security threat here in a way, that the SYMMETRIC KEY is visible to DBA and other database Users. They can always decrypt that column using the key any time they want. This is not feasible in my application. Can someone please suggest what can be done to safeguard this key?


texts: Is it possible to JOIN to a Primary database from a Replication instance?

texts:
I have a database on a Replication server that doesn't exist on the Primary server.

There are stored procedures on the Replication server's DB that join to tables in other databases on the Replication server. These should be joining to tables on the Primary server.

Is this possible? If so, how?

This is an example of what it's currently doing:

UPDATE  c
SET     CRP_Id      = be.EventId
FROM    #TempEvent be
JOIN        CRP.dbo.Payments c -- *should* be joining to CRP on Primary, not Replication
    ON  c.CRP_TransactionId     = be.SourceTransactionId
WHERE   be.EventTypeId  in  (
                                @MyEventTypeId
                            );


texts: Where to install Oracle client software on SharePoint 2010 Server

texts:
We have our ASP.net web application hosted on a SharePoint Server 2010. Our application uses oracle database to save and retrieve data.For our application to work on SharePoint server we need to install Oracle client software.

Our SharePoint Server architecture consists as following

2 web front ends
Index Server
Crawl Server
Application server
Load Balancer.

I am not the admin on the SharePoint server and trying to figure out if i have to install Oracle client on server where do i install it? Do i need to install it on all our servers or just the web fronends. Can some one please help me with this?


texts:
In SQL Server, I understand when the "Enforce password policy" is checked for accounts with SQL authentication, it will pull from the O/S.

If "enforce password policy" is checked and "enforce password expiration" is not checked, will the SQL account's password inherit the expiration of 90 days? Or do both have to be checked?

Does "enforce password policy" override the "enforce password expiration" whether it's checked or not?


texts: Organizing a couple tables in postgresql

texts:
I have a data structure that is represented like this:

{
      'question_id': 1,
       'is_required': False,
       'question_text': [
            {
                'id': 1,
                'percentage': 50,
                'text': [
                    {'en': 'Some text here'},
                    {'es': 'Mucho text here'},
                ],
            },
            {
                'id': 2,
                'percentage': 50,
                'text': [
                    {'en': 'More text here'},
                    {'es': 'Mas text here'},
                ],
            }
        ],
}

and I want to store this in the database so I can say "Give me this question with all it's text variations for a specific language.  What would be a good way to store this?


texts:
I haven’t yet tried out SQL Server on Linux, but I have a question regarding case sensitivity.

In MySQL and Oracle (as far as I am aware) table names and database names are case insensitive on Mac & Windows, but case sensitive on Linux. I understand this has something to do with the case sensitivity of the underlying OS.

PostgreSQL, on the other hand, doesn’t work that way.

The question is: are database and table names in SQL Server on Linux case sensitive?

I ask this because I always recommend sticking to the case in which names are defined, but I want to check whether it is also future-proofing the project.


texts: Setting default charset/collation for MySQL database

texts:
I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I have a crazy mixture of latin and utf8. Basically, any new tables and columns are different.

Is there a way to set the default charset and collation for the database, so that when I add new tables to this database they are utf8?


texts: PostgreSql allocate memory for each connection

texts:
When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ?

I know 25% of the memory of the Server should be allocated. But how do we allocate based on the DB connections? How do we know, what is the maximum number of connections should be allocated?

Also in a multi-nodes environment can we allocate more connections for each node (in Postgres-ds.xml max connections) than what is actually allocated in the DB?


texts: Troubleshooting oracle database errors from cloud application

texts:
I am trying to troubleshoot an issue that presents as different database errors such as, ORA-01000: maximum open cursors exceeded or Unable to create DB Connection.  I have reviewed the PLSQL to determine if cursors were left open and all are closed even if there is an error.

The java application and background are as follows:
The original application was a 3-tiered system:

  GUI app. -> server app -> 11g Oracle database

The enhancement was to add an API service in a Pivotal Cloud Foundry (PCF) environment.
So this architecture was like this:

  Close Function: GUI app -> Server App -> API service -> Database.

  All other Functions: GUI app -> Server App -> Database.

This was put into production and run for a week without any database issues described above.
Then another enhancement was added in which the API Service communicates with several other services all in PCF in which 2 communicate with the same oracle database.  Now during heavy volume we are getting these database errors.

It seems to me that the Oracle database cannot keep up with the requests from these additional services.  But how can I demonstrate that.  We have AppD configured for the servers but not the database.  Are there queries that I can run in the prod env. that shows that these PCF applications are causing the issue?  Or should I look in another area?

Thanks,

UPDATE
I created a query that shows the total open cursor by sessionID
This is the query:

select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,
     sys.v_$sesstat a,
     sys.v_$session b
 where a.statistic#=c.statistic# and
      b.sid=a.sid and
      c.name in ('opened cursors current') and
      (b.username is not null or b.username <> '' )
group by b.sid, b.username, b.osuser
order by total_opened_current_cursors desc

Now, I need to link the sessionID with the application that has this session.
The osuser for the top ones is NULL.
Also, most of the sessions' status are INACTIVE
How to identify the application to the session?
Secondly, is the session is inactive, which I thought meant that no query is happending so why are there open cursors?

UPDATE
SORRY I ADDED THIS UPDATE TO THE ANSWER...SHOULD HAVE ADDED IT HERE
So, I wrote a query that returns the top 10 sessions with the highest open cursors

select *
FROM
(
select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,
     sys.v_$sesstat a,
     sys.v_$session b
 where a.statistic#=c.statistic# and
      b.sid=a.sid and
      c.name in ('opened cursors current') and
      (b.username is not null or b.username <> '' )
group by b.sid, b.username, b.osuser,  b.status
order by total_opened_current_cursors desc
)
WHERE ROWNUM <= 10;

I found the SQL_TEXT that accounts for most of the open cursors...by far! (87%)
So, how do I find the query that calls this SQL?
There are at least 5 services that hit the database. Some of the services call PLSQL stored procedures some call raw SQL text.  The query that accounts for the open cursors is listed as a SELECT statement.  Does that mean it is NOT a stored procedure?  Or can this SELECT be called within the stored procedures.
How do I find the connection that uses this session?


texts:
Can someone take a look at this code and provide a hint as to what it is I am doing wrong. This code taken almost literally from a reference book, as an example on how to use VBA ADO object. Here is the code:
Sub TestMyADODB()
Dim mySQL As String
Dim myConnection As DAO.Connection
Dim myRecordSet As DAO.Recordset
Set myConnection = CurrentProject.Connection ' Crash here
mySQL = mySQL & "UPDATE Amplifier "
mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 "
mySQL = mySQL & "WHERE (((Amplifier.lngPartIndex)=9))"
    myRecordSet.OpenRecordset mySQL
Debug.Print "DONE"
    myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

Furthermore, I tried using the ADODB object, result, the compiler will not permit the code. I found I needed to add a reference to the MS dll, which I did, still the same result. Then I started getting an error from VBA stating that I cannot use DAO which is now " Outdated " and that I must use ADO.
The rest is 10 hours of lost precious time trying to demystify the ADO DAO issue from multiple articles on tne web. Bottom line, my Access 2007 will literally crash if I use DAO and the compiler will refuse to use ADO.
I am loosing my hairs over this issue, and I need help!  Appreciate
PS: The mySQL string has been tested in an Access query and functional.


texts:
First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex topic it couldn't have maybe 3 - 4 possible solutions and I hope it's okay here.

My basic convention singular/plural of table names is as follows:

products - plural
product_variants - mixed - variants of product

This convention worked great, but I got a problem right here:

I have a products table (yes, I am naming them in plural. Do you think it's bad? Give me arguments why, please)
I have categories_products table - (I also have categories for ingredients table, that's why it isn't named just categories)

I didn't want to name it category_products, because it may sound like n:1 products of category

I have product_categories table, which stores n:n of products AND categories_products

I tried the same with singular:

product
category_product - sounds like n:n table
product_category - sounds like n:n again

Or

product
product_category - would be list of categories then
product2product_category - n:n table

Sound the most clean

But since it's a complex application and I don't want to change all the tables to singular if not necessary, I tried even plural

products
product_categories
products2product_categories

But I don't like that one.

Is there any better way to do such naming, without making observer, or even me, confused?

The very similar question is about enum definition of some dataset, without confusion

products table have product_status_id - so it's more dynamic than enum
table product_statuses - sounds like 1:n table - statuses of product

Can somebody turn me into the right direction, and explain why is the direction best?


texts: Delete Old Archivelogs from missing database

texts:
We have deleted a database but the archive logs remain. We need to delete these as they take a lot of space:

Since the database is deleted, it is not in the rman catalogue so I cannot use delete archivelog expired in Rman to delete these files.

I read about asmcmd but I'm afraid to put out of sync the Enterprise Manager's records with the actual disk content going that way.

What is the cleanest way to proceed?


texts: Why does sql server perform better after restart

texts:
We had a performance issue that we solved by restarting the sql server service.
That's obviously not the way we want to continue handling our performance issues.

When a sql server performs better after restart the probable causes to me are

wrong cached query plans
memory pressure

To rule out wrong query plans being cached query plans we first dropped the query cache with

DBCC FREEPROCCACHE;

but the problem persisted.

I am still not sure about memory pressure. Page life expectancy goes down during the bad performance period to 50s. But cache hit ratio is 100%.

Are the numbers suggesting memory pressure here or are there other possible reasons?

I put a beautiful red line where we restarted the sql server service.

At the time of the issue we did not have more requests than usual.


texts: Mirroring between two Ms-sql servers for Migrating server from one Datacenter to other Datacenter

texts:
I have a query about microsoft sql server. I'm looking for migrating ms sql server from one vendor to another vendor(Datacenter). I'm looking to have a mirror configuration between servers since my database is close to 40GB and would take days for copying from old to new, So once the database is synchronized, i would be taking down the old server and connect my app with new server.

My concern is if i take backup and restore it in the new server and start mirroring, Would the data will be mirrored where transactions happened between taking backup of the server and starting mirroring?

Thanks,
Swaroop.


texts:
Here is my problem: I am using SQL for Access 2013; I have a table tbl with some fields Category, Subcategory and Value:

Category  |  Subcategory   |  Value
          |                |
   C1     |      Sc1       |    x1
   C2     |      Sc3       |    x2
   C1     |      Sc2       |    x3
   C3     |      Sc7       |    x4
   C1     |      Sc1       |    x5
...

Each category includes a unique set of subcategories, such that subcategories are not repeated between categories. However, subcategories can be repeated within a category (see for example first and last line from tbl above, where Sc1 is repeated).

I would like to retrieve, for each category, the top 10 subcategories by total value and create a table tblRankings like this:

   C1    |   C2   |   C3   | ...
         |        |        | ...
 Top1,1  | Top2,1 | Top3,1 | ...
 Top1,2  | Top2,2 | Top3,2 | ...
 Top1,2  | Top2,3 | Top3,3 | ...
...

The basic query qryRankingC1 for selecting the top 10 subcategories for a given category (in this case C1) based on total value is:

SELECT TOP 10 [Subcategory] AS C1
FROM tbl
WHERE [Category]='C1'
GROUP BY [Subcategory]
ORDER BY SUM([Value]) DESC;

However, once I have done this I am blocked; I have tried the following query to create the table:

SELECT qryRankingC1.* INTO tblRankings
FROM qryRankingC1

However this can only handle one category at a time. I would like to create a table with as many columns as categories in a single query if possible.

Moreover, instead of having qryRankingC1,qryRankingC2,... I would like to have a single qryRanking query, however if I do:

(SELECT TOP 10 [Subcategory] AS C1
FROM tbl
WHERE [Category]='C1'
GROUP BY [Subcategory]
ORDER BY SUM([Value]) DESC)
UNION
(SELECT TOP 10 [Subcategory] AS C2
FROM tbl
WHERE [Category]='C2'
GROUP BY [Subcategory]
ORDER BY SUM([Value]) DESC);

I get the top 10 subcategories for C1 and C2, but on a single column named C1.

Could anyone shed some light on this?


texts: How can I group together rankings by number of users?

texts:
I have a table which includes the following:

ID    evar     event_date   ranking
1    landing   2019-01-01      1
1    content   2019-01-02      2
1    homepage  2019-01-03      3
2    support   2019-02-01      1
2    content   2019-02-02      2
3    content   2019-01-23      1
3    support   2019-01-24      2

etc.

I am trying to map customer journeys, so I've ranked the pages that were visited by date.
I need to find the top journeys on the website - so it needs to be at this level, but aggregated by the number of users.

For example: ID 1 made the journey landing > content > homepage;
I would like to see who else made that same journey (without me manually checking)

How can I write such a query?


texts:
I am trying to create a menu bar which will load dynamically on user log in, based on the user roles. Say if the user is an admin he can see all the items listed under the header and if a user is a guest he can see only few items e.g. Item 1.1, Item 1.2, Item 2.2, etc. The menu bar will be something like,

Header
  |_Sub-header_1
  |       |_Item_1.1
  |       |_Item_1.2
  |       |_Item_1.3
  |_Sub-header_2
  |       |_Item_2.1
  |       |_Item_2.2
  |       |_Item_2.3
  |       |_Item_2.4
  |_Sub-header_3
  |       |_Item_3.1
  |       |_Item_3.2
  |       |
  |

So there will be header and there will be 7 sub-headers, in sub-headers there could be 10-20 items. I want a database where I can store these items, sub-headers and header.

1) Please try to make me understand how to design a database for such scenarios?
2) Also I want to create a url out of it, so how do I do that?


texts: How can I pass parameters to an "SQL Script" from isql

texts:
I am wondering if it is possible to pass parameters to an SQL script from within sybase's isql utility.

For example, I'd like to store a select statement in the file the_script.sql that would look something like

select
   col_1,
   col_2,
   etc
from
   table
where
   cond1 > $param1 and
   cond2 < $param2

Then, with that file, I'd like to "execute" it from isql with

:r the_script.sql 900 20

with the expectation that $param1 is repleaced with 900 and $param2 with 20.

Is there a possibility to achieve what I want?


texts: Deploying Oracle ADW Changes from development environment to production

texts:
I'm currently running an Oracle Autonomous Data Warehouse (Oracle's cloud-based database offering) on version 19c. I have a development database on the Always Free Tier and my production database is a paid instance (with backups etc). I will likely also be making a staging instance soon.
When making changes to the database schema we have been making changes to my database schema as follows:

Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
Test the development environment manually
Change the schema on the production environment using identical SQL statements
Test the production environment to make sure nothing is broken

I am aware that this is not the way it should be done, we need to implement some CI/CD to streamline these changes but we've only recently come out of our piloting phase where it was a rush to get the product working etc, I'm very keen to firm up our new systems.
How I'm imagining it'd go (changes in bold):

Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
Test the development environment with automated tests
Apply some tool to apply the same changes to my production system
Test the production environment to make sure nothing is broken

I have seen that tools exist for other SQL database tools, such as MySQL, PostgreSQL etc but from my searches on Oracle-specific tools, I'm not sure if where to find a tool that can propagate the schema changes from our development database to a production database.
If you know of any tools that I can look at to do this functionality then I'd appreciate a pointer on where to look!
I'm aware this is similar to other questions, the ones I've looked at are:

Altering db schema in production environment
Upgrade production database during software development lifecycle

Both of these questions were asked several years ago and so I'm not sure if new things have come up recently.


texts:
I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of states. However, there is a function that inserts a record in that table that fires that trigger (it starts in a different state).

My question now, is it OK to disable that trigger using ALTER TABLE in that function to get this working, or should I rethink my trigger design? Another work around would be to insert it in the "start" state and immediately UPDATE the record to make a state-step.


texts: MySQL: cannot run change master to inside mysql

texts:
I am trying to create a stored procedure that can run change master to command.

This is what I was able to come up with.

DELIMITER //
CREATE PROCEDURE changeMasterTo(IN ip char(20),IN port int,IN user
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
CHANGE MASTER TO MASTER_HOST=ip,
MASTER_PORT=port,
MASTER_USER=user,
MASTER_PASSWORD=password,
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
END;
//

But it fails with the following error.

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

  'ip,
  MASTER_PORT=port,
  MASTER_USER=user,
  MASTER_PASSWORD=password,
  MASTER_AUTO_P'

  at line 5

I am not sure what the issue is, any help will be appreciated.


texts: Optimize query plan when your results are either a few rows or hundreds of rows?

texts: The most frequent query typically uses a variable that has hundreds of rows, but also runs often with a variable that has only a few rows (it's always one or the other, nothing in between). SQL sporadically picks up the query plan for the query against the few rows, resulting in spiking CPU when that plan runs against hundreds of rows, and my guess is it's after statistics update (auto stats are ON, I'm turning them off and adding a job to have it done at low peak rather than automatically at random times). Aside from pinning the plan in query store for the higher number of rows, is there a way to have both queries run successfully?  Azure SQL V12.


texts: Large MySQL operations slowing down SELECT queries on unrelated tables

texts:
On a fairly busy SQL server, we sometimes need to do large index-building operations (after importing ~6GB tables).  The indexes are built using the more efficient "Repair by sorting" method, rather than keycache.

We have found that when these operations are running, SELECT queries on other tables in the database can get bogged down.  These tend to be queries that were already fairly expensive in terms of rows scanned; for example, we see queries that normally would take ~5s taking ~25s instead.  All relevant tables are MyISAM, as they are effectively read-only.  (We do these large imports daily, and when they are finished, atomically replace the live table with the newly imported one.  Aside from that, the live tables are not modified.)

We track CPU load and usage, memory, and disk access times, and there appears to be plenty of headroom on all fronts.  So the problem doesn't appear to be that we are simply maxing out on disk I/O for instance.  Therefore my guess is that we're running into a MySQL-specific limit - some sort of buffer or cache that's shared between connections.  I'm not a DBA though, so that's about as far as I've gotten.  Any ideas on what could be causing this?  I'm happy to provide any relevant info.  Here's our my.cnf:

[mysql]
no-auto-rehash
[mysqld]
tmp_table_size=2048M
max_heap_table_size=2048M
init-file="/etc/mysqlinit.sql"
thread_cache_size=8
query_cache_size=256M
query_cache_type=DEMAND
innodb_log_file_size=10485760
character-set-server=utf8
default-storage-engine=MyISAM
myisam_sort_buffer_size=64M
skip-external-locking
ft_min_word_len=3
innodb_buffer_pool_size=2048M
thread_concurrency=8
open_files_limit=10000
max_allowed_packet=268435456
sort_buffer_size=2M
read_rnd_buffer_size=2M
socket="/db/mysql/mysql.sock"
key_buffer=512M
tmpdir="/db/mysql-tmp"
datadir="/db/mysql"
read_buffer_size=2M
innodb_file_per_table=1
wait_timeout=30
max_connections=500
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[isamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[client]
socket="/db/mysql/mysql.sock"

The server has 16GB of RAM, of which we rarely use more than 6 except for caching.  (Although would like to leave some headroom for traffic spikes, as the web server runs on the same box.)  It has 8 cores, and the databases are on a RAID 10 array of 120GB SSDs (at /db).

Any tuning suggestions would be appreciated.  (Please give reasoning though, not just suggested settings.)

Edit: based on this page, it appears that key_buffer_size might be the culprit.  This buffer is indeed shared between connections (by default), and I expect the indexing operation is using it up, bogging down other queries.  I will experiment with the multiple caches approach recommended there and report back:

  For key_buffer_size, you must take into account that the buffer is
  shared with those users. [...]
  An alternative that avoids this problem is to use a separate key cache,
  assign to it the indexes from the table to be repaired, and deallocate
  it when the repair is complete. See Section 8.9.2.2, “Multiple Key
  Caches”.

Edit 2:
Tangentially related: based on this MariaDB doc page, I increased our overall key buffer size from 512MB to 2GB and noticed an order of magnitude better ratio of key_read_requests to key_reads in the status.  (From about 1:150 to 1:1000. So that change improved key cache hits by about 7 times.)

Edit 3:
While increasing the key buffer size seems to have helped performance overall, using a separate key buffer for the table being imported does not prevent it from slowing queries on other tables, so the initial question remains unanswered.


texts: conversion of a non correlated sub query to a join statement

texts:
Is it possible to get rid of the sub query and convert the entire query into a JOIN query?

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   configurations);

I tried the following:

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance,
       configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = c.max_allowed;

and got this error:

  Column 'c.max_allowed' is invalid in the HAVING clause because
  it is not contained in either an aggregate function or the GROUP BY
  clause.

I want to do this for to a possible increase in query speed in case of thousands of records. Is my concern even valid?

Environment: SQL Server 2000


texts: Relationships overview with some many-to-many

texts:
Good morning/afternoon,

I am currently beginning a new project and I wanted to design the database before doing anything however I struggle a lot with this design because it has some many-to-many relationships and I don't know how to assemble everything nicely.

Here is the design I came up with:

Here are the relationships I'm trying to achieve:
A project is linked to 0 or MULTIPLE hosts
A host is linked to ONLY 1 project
A host is linked to 0 or MULTIPLE services
A host is linked to 0 or MULTIPLE vulnerabilities
A service is linked to 1 or MULTIPLE hosts (it is ALWAYS linked to a host)
A service is linked to 0 or MULTIPLE vulnerabilities
A vulnerability is linked to 1 or MULTIPLE hosts (it is ALWAYS linked to a host)
A vulnerability is linked to 0 or MULTIPLE services
A credential is linked to 1 or MULTIPLE hosts (it is ALWAYS linked to a host)
A credential is linked to 0 or MULTIPLE service

Is my table HostDetails suitable for that job or will it be a nightmare to retrieve/insert/update data ?

What would you do to fulfill the job ?

Thank you very much for your help !

If you want to reproduce the schema, go to dbdiagram.io and paste de following code:

// > many-to-one; < one-to-many; - one-to-one

Table Projects as U {
  project_id int [pk, increment] // auto-increment
  uuid uuid
  name varchar(100)
  description varchar(255)
  start_date date
  finish_date date
}

Table Hosts {
  host_id int [pk, increment]
  host_uuid uuid
  ip_address inet
  mac_address macaddr
  status smallint
  operating_system varchar(50)
  usage varchar(50)
  project_id int [ref: > Projects.project_id]
}

Table Services {
  service_id int [pk, increment]
  service_uuid uuid
  port smallint
  protocol varchar(5)
  service varchar(50)
  version varchar(50)
  status smallint
 }

Table HostsCredentials {
  credential_id int [pk, increment]
  credential_uuid uuid
  username varchar(50)
  password varchar(50)
  hash varchar(255)
 }

Table HostsDetails {
  host_detail_id int [pk, increment]
  host_id int [ref: > Hosts.host_id]
  service_id int [ref: > Services.service_id]
  vulnerability_id int [ref: > Vulnerabilites.vulnerability_id]
  credential_id int [ref: > HostsCredentials.credential_id]
 }

Table Vulnerabilites {
  vulnerability_id int [pk, increment]
  vulnerability_uuid uuid
  cvss_vector varchar(50)
  status smallint
  description text
  evidence text
  solution text
  references text
}


texts:
I setup a test job to test out my failure notifications and now I'm getting the following error, stating it did not notify my operator via email. The strange thing is I ran the same exact setup script on several other instances and it worked just fine, but is failing on this instance. I checked the properties for SQL Server Agent and the operator itself. I also sent myself a test email from the Management branch's Database Mail and it worked successfully.

  Failed to notify 'DBA Team' via email.


texts: Automate: importing xls to table (without microsoft excel)

texts:
I am importing 5 different reports from payment sites (paypal, worldpay, etc)

It works like a charm using csv!

BULK

INSERT Report_Table

FROM '\\Files_Server\departments\finance\Paypal.csv'

WITH

(

FIRSTROW=2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '0x0a'

)

But there is this one site that gives us an .xls file (Excel 97-2003 Worksheet).

I can’t use SSIS because in the server I don’t have excel installed and I don’t have permission to install it… My question is, if I convert it myself from xls to csv, will I be able to BULK INSERT it easlly, or it will contain garbage remaning from the xls to csv conversion?!

Options I see:

1-      ssis (I can’t install excel on the server).

2-      convert xls to csv and then bulk insert? (can I?)

What other options do you see/recommend?


texts:
I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language.
For example,  If I want to generate a SQL GRANT command for every table in a database, and for every category of user in the database,  the generic grant command might look like this:
grant $privs
   on $table
   to $user;

Here, $privs, $table, and $user are parameters to the Grant command,  expressed as powershell variables.
I want to step through a list of data that could be applied to this generic form to produce the actual script.  The list of data might look like this:
privs       table       user
-----       -----       ----
ALL         Employees   DBA
READ        Employees   Analyst
READ, WRITE Employees   Application
ALL         Departments DBA
READ        Departments Analyst, Application

This should generate five SQL GRANT commands.
Full Disclosure:  I am asking this question so as to be able to provide my own answer.


texts: Does MySQL limit the number of users (not connections)

texts:
I have always believed that you can create as many users as you like. I have been creating a user per app with limited database/table access for only what that app requires.

Recently I was told there is a limit. While I have never heard of one, or reached one, I still wanted to ask the community.

Note: I am not referring to max connections, but the number of users.


texts: SET STATISTICS TIME ON elapsed time is different than actual execution time

texts:
I have a stored procedure which I'm running while using SET STATISTICS TIME ON, and overall the execution time of the procedure is 31 seconds, but when looking at the information from STATISTICS TIME, the result of adding up all of the elapsed times for each query is greater than 31 seconds.

For example:

SQL Server Execution Times:   CPU time = 109 ms,  elapsed time = 128 ms.

SQL Server Execution Times:   CPU time = 156 ms,  elapsed time = 159 ms.

SQL Server Execution Times:   CPU time = 1528 ms,  elapsed time = 15901 ms.

SQL Server Execution Times:   CPU time = 1248 ms,  elapsed time = 8055 ms.

SQL Server Execution Times:   CPU time = 6037 ms,  elapsed time = 30433 ms.

SQL Server Execution Times:   CPU time = 6069 ms,  elapsed time = 30776 ms.

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

I feel like I'm getting things mixed up while trying to read the information here, since the execution time of the query definitely isn't 85452 ms.

Also, I know that if the CPU time is greater than the elapsed time then the query went parallel, and even when double-checking this on the actual execution plan, there aren't any parallel operations executed.

So, why is the elapsed time in STATISTICS TIME different from the actual execution time of a query?


texts:
We are on Windows Server 2019 on Azure VM, with SQL Server 2019 Enterprise CU4 installed. SQL Server differential backup jobs are failing intermittently.
I have looked into the SQL Server error log and I can see  the following error for all databases:

I/O is frozen on database DBName. No user action is required. However,
if I/O is not resumed promptly, you could cancel the backup.

followed by the following error logs:

I/O was resumed on database DBName. No user action is required.

After this error occurs, the differential backup job starts failing with the following error:

Executing the query "BACKUP DATABASE [DBName] TO  DISK =
N'X:\MSSQL..." failed with the following error: "Cannot perform a
differential backup for database "DBName", because a current database
backup does not exist. Perform a full database backup by reissuing
BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP
DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

At this point we cannot take any further differential backups until we have taken a full backup of the database.
I have checked and there is no other SQL Agent job running at that, and I cannot understand why this error is occurring.
I have done some research online and it appears that it was a known issues in SQL Server version 2016, 2017 and 2019 but it has been fixed in CUs for all those versions. The following article talks about this issue and confirms that issue has been fixed in SQL Server 2019 in CU2.
https://support.microsoft.com/en-us/help/4523102/fix-sql-server-database-remains-in-frozen-i-o-state-indefinitely-when
We have SQL Server 2019 with CU4 applied. So all the fixes should be in there from CU2. However, we are still experiencing the I/O frozen issue.
Any pointers or suggestions are much appreciated. Thanks in advance.


texts:
We have a server that connects to 40+ different remote computers which are running Sybase 11.0.1.2596. Our server is hosted on an aws ec2 AMI Linux instance, and the connections are set up using FreeTDS. The server has several different command-line PHP scripts that use PDO with dblib to connect and select data. 99+% of the time everything works, but sometimes I get these errors:

SQLSTATE[HY000]: General error: PDO_DBLIB: dbresults() returned FAIL

SQLSTATE[HY000]: General error: 20047 TDS: unexpected token 56 [20047] (severity 1) [(null)]

SQLSTATE[HY000]: General error: 20047 DBPROCESS is dead or not enabled [20047]

They seem to happen randomly, and I can't figure out what is causing them or how to fix it. If I just wait, it will start working again. It also seems if I use tsql to connect it will start responding:

tsql -S <odbc name> -U <username>

Any advice is greatly appreciated.


texts:
I am trying to connect to a SQL 2017 instance via SSMS 18.2 with the 'Additional Connection Parameters'
StatsLog_On=Yes;StatsLogFile=C:\Temp\TestODBC.txt
The documentation at Using Connection String Keywords with SQL Server Native Client is less than perfect, but it strongly implies that it should work.  I know from other sources that the keywords StatsLog_On & StatsLogFile need to be used together.
I get this error message when I try to connect

===================================
Cannot connect to sandbox.
===================================
Keyword not supported: 'statslog_on'. (System.Data)

Program Location:
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.SqlServerType.Microsoft.SqlServer.Management.UI.ConnectionDlg.IServerType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Note: "Sandbpx" is the name of the SQL instance
As a reality check using Server=sandbox;MultiSubnetFailover=True works fine


texts: SQL Server : create table within stored procedure

texts:
I have a stored procedure that will be run by a user with public permissions.
When operational, it will be called from an Excel spreadsheet and the results presented on a given tab.

The procedure has many execution paths, one of which is to create a table - [temp].[xyz] (temp already exists as a schema - users with 'public' have alter permission on this schema)

This same s/p is later called many times and selects various results from [temp].[xyz]

Finally, the s/p is called to drop [temp].[xyz]

However, the problem starts when it tries to create [temp].[xyz] using

select ...
into [temp].[xyz]

Error: I don't have permission to create a table. (Note: The table does not exist already)

I think the solution may be something along the lines of : a sys admin creating a new login who has table create permissions.  When it comes to the create(select into) table, wrap this statement in an 'EXECUTE AS' statement this the login who has create privs.

I'm OK with SQL but very basic on sys admin. If you need more details, please ask. Any help as to how I can get round this is appreciated.

Thanks, J


texts: Setting up filtering for server-side tracing

texts:
I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd...

I was trying to set up a filtering on both the duration of the SQL batch or stored procedure (filter anything >= 1000 ms), but I also wanted to filter on the database name involved.

When I export the trace definition from SQL Profiler to a *.sql file, I however don't seem to find any filtering on the database name:

-- lots of other stuff up here - setting up trace events and so on...

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 100479e4-6eeb-4608-af17-64141f1631ec'
set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

....

So what's up with that? Why can I specify a database name to filter on - but the trace definition doesn't contain it, in the end?

Does that mean the server-side trace is always only checking the database it's been creating in?
But then why can I specify a filter by database name?
Does this also mean there's no way to do cross-database server-wide tracing on e.g. deadlocks?

Update: after some more toying around, I came to the following conclusion:

the filter on the database name was not applied to the Create Trace script in my case since I ended up selecting only the Locks / Deadlock Graph event - and that event doesn't expose a Database Name as an event column
if I pick other events, like RPC completed that do expose the Database Name as an event column, I can filter on the Database Name and that filter is also properly added to the Create Trace script.

I can't figure out how I was able to actually define a filter on Database Name, since normally, if an event doesn't expose a column, that column is also not available in the Column filter dialog....

Anyway - for whatever reasons that are beyond me (for now, at least), Deadlock Graph doesn't expose the Database Name and thus I cannot filter on that property, either....

Thanks all that helped and provided insightful comments!


texts: MySQL InnoDB page_cleaner settings might not be optimal

texts:
Seeing this note in mysqld.log:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)

There seems to be mention of something like this here:
MySQL instance stalling "doing SYNC index"

My question is: what action should be taken, if any, when this note is seen in the logs?

MySQL and OS versions:
mysql-community-server-5.7.9-1.el7.x86_64
centos-release-7-1.1503.el7.centos.2.8.x86_64

Running SHOW VARIABLES LIKE 'innodb%'; as suggested shows:

innodb_page_cleaners | 1


texts: When does an execution plan not show in sp_WhoIsActive?

texts:
I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries.

Why does the execution plan not show all the time in the results? Most of the time, the query_plan value is NULL. If I look at the SQL_TEXT column in the results, I do see the query with parameters, but no values, like below:

(@P0 nvarchar(4000),@P1 nvarchar(4000))
select blah from foo where a = @P0 and b = @P1

How do I get the execution plans when they don't display here? Why don't they display? Must I use Profiler or Extended Events? Would putting in dummy values into these parameters and running the query with the actual execution plan included generate the same plan that was actually used when I was logging?

I have verified with the vendor that they are using Hibernate 3.5.


texts: Deploying and rolling back changes in an Availability Group database

texts:
I'm looking for a solution or workflow. Here's how I used to do database deploys when using a stand-alone server:

Create a snapshot of the production database
Deploy changes
Verify that the deploy was successful
If the deploy was not successful, restore the database from the snapshot.

I would like to perform a similar process on a database in an Availability Group, but restoring from a backup or snapshot is not a desirable option because the database is fairly large and I'd have to take the database out of the AG first. Here's the ideal type of solution I am looking for:

Suspend data movement in the AG
Deploy changes to primary replica
Verify that the deploy was successful
If the deploy was not successful, rollback the primary replica to the LSN where we suspended data movement and restart synchronization.

Is there a practical way of doing this?

I would use BEGIN/ROLLBACK TRANSACTION, but this is harder to implement with the automated build process, and given that I would first like to inspect the results (from a different database connection) before committing or rolling back.


texts:
I've posted this on stackOverflow but this might be a more suitable place.

I'm really struggling to find a good DB design for my project using SQL server.

I've already implemented a few models which worked great till this point, but now that I need to add something extra I just can't find a good option and I'm stuck with it ATM.

I've supplied below 2 very simplified models (class diagrams) I've tried, but both models are not working well.

1st model: which I also prefer if it's possible to fix

I should explain first that msg and action can have the same basic id (i.e 1) but when used with tabID or groupID as a composite primary key, it becomes unique.

Here you can see that UserInput is created using only the basic ID which creates a problem to save both a Msg and Action with id 1 for example.
Is there anyway around this? maybe a way to say that ya Action and Msg are extending UserInput but they define all the keys themselves?

2nd model:

Each Critical Point is related to either a Msg or Action, but how can I define it since they have a different set of PK? I would like to keep referral integrity.

I would REALLY appreciate help on this issue.

Update

Maybe my only option is to use this?

UserInput
  UserInputID:   PK (autogenerated)

Action
  ActionID:      PK
  TabID:         PK, FK
  UserInputID:   FK

Message
  MessageID      PK
  GroupID:       PK, FK
  UserInputID:   FK

The question is can I make the Msg and Action extend from UserInput without taking defining his PK as part of their PK...


texts: Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

texts:
I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around 1.5GB.

So it's quite important to know if MySQL loads the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY and indexes when performing the query, and the rest of the columns at the very end?

An example query:

SELECT HugeDataTable.*, Table2.Name
FROM Table1
LEFT JOIN Table2 ON Table1.`ID` = Table2.`Table1ID`
LEFT JOIN HugeDataTable FORCE INDEX(RowOrder) ON Table2.`ID` = HugeDataTable.`Table2ID`
WHERE HugeDataTable.Category = 5
AND HugeDataTable.RowOrder >10000 AND HugeDataTable.ID <> "h4324h534"
ORDER BY HugeDataTable.`RowOrder` DESC LIMIT 18 ;

Using Explain SELECT I've managed to find that MySQL scans around 70k rows per query. The query is rather fast, but I'm not sure if it's due to row caching, as I can't simulate a heavy load on the server.

So, my question is, will the columns containing the large raw data be loaded after the query limits the result to 18 rows, and thus loading only the little raw data needed in the end?

Or will they be loaded before the limit, and so 70k rows, which are around 1GB's worth of data be loaded before the limit? And if it's the latter case, what can be made to prevent such a thing, since the server only has 1GBs of RAM.

Edit: I've added the EXPLAIN.

id  select_type  table          type    possible_keys                       key               key_len  ref                           rows   Extra
1   SIMPLE       HugeDataTable  range   Table2ID,Category,RowOrder          RowOrder          9                                      49591  Using where
1   SIMPLE       Table2         eq_ref  PRIMARY                             PRIMARY           10       const,HugeDataTable.Table2ID  1      Using where; Using index
1   SIMPLE       Table1         ref     PRIMARY                             PRIMARY           2        Table2.Table1ID               1


texts:
I configured in /etc/mongod.conf to enforce keyfile access control, in security option enabled, keyFile is /root/dbtest.key (the absolute path of keyfile). I already gave the ownership to mongodb user by chown, and granted 400 permission on that dbtest.key file.

But mongod keeps failing to start, after checking log, the error is Error reading file /root/dbtest.key: Permission denied. After checking the ownership and permissions on dbtest.key

Which means I already granted correctly. So I don't know at which step I did wrong


texts:
I have seen many tutorials on how to create a table-valued parameter - but how do you view a table-valued parameter after it has been created?

I would prefer a way of using the GUI in SSMS but T-SQL would suffice as well.


texts: How to create two database copies in two users from same dump file

texts:
I have a database dump file with me, I need to make two copies of the database using the same dump file in two users.

Is it possible?


texts:
I am getting continuously below error message in mysqld.log

[ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase innodb_log_file_size.

The size of tables which are having blob datatype is 690 MB.
The size of each ib_logfile is 48MB

As per the error message should I increase my ib_logfiles to more than 69MB. If so, when again if data of blob tables increases to 10GB, should I increase my log files to 1 GB.

Please suggest me how to resolve this, and how are these dependent to each other.

OS is Linux
MySQL version is 5.6.21

Later, I had changed all the columns whose datatype from BLOB to TEXT. Now there are BLOB datatype tables only in 'mysql' schema. Even after converting the datatype, I am observing the above error message.


texts: Maximum Server Memory adjust in blocks of 1024MB?

texts:
Our protocol is to set Maximum Server Memory at about 75% of RAM.  For a 4GB RAM, sometimes it gets set at 3000MB and sometimes to 3072MB.  There is a 2 or 3 percent difference in these values.

Is the impact of setting memory at a value that is not evenly dividable by 128, greater then the difference between the rounded and binary values?

Asked differently; 128*23=2944 & 128*24=3072, Does using 3000MB provided a benefit that is between 2944 & 3072 or does it's fractional size disrupt memory usage?


texts: MySQL group concatenation - creation of insert statement on temp table

texts:
I have a temporary table TEST_GROUP, from where I'm working its data to fill another one.

From this table, giving the example in the picture, my expected result is to group these three rows, so I can have a result like

INSERT INTO TABLEDATA Select * from TEST2 where (field1 = 'B') and (field2 = 'T') and (field3 != 'S' and field3 != '')

Easy to say that when the EXP = EQ only validates the VAL1, but when the EXP=NE should validate VAL1 and VAL2, even if VAL2 is empty.

Not so easy for me to get the expected result.

Can you provide some guidelines how to get the expected result?


texts: Following mysql query is taking too much time for excecution

texts:
I have a MySQL table which is described in the following question.
Counting number of ids present in one table which is not presented on another for a particular user
The query which I am using is as follows.

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id)

This query is taking too much time for considerably bigger table how can I improve the performance of this particular query.


texts: How can I rename a table, but also all FK etc. references to that table?

texts:
I would like to e.g. rename my Applicant table to ApplicantProfile, as applicant is an abstract entity that has a profile. The profile is not the applicant.

Simply renaming the table is hopelessly inadequate because there are qillions of relationships to Applicant. My closest solution is scripting the whole DB, and doing a search and replace, but this seems risky, as I have fields like ApplicantId that mustn't be renamed. I can't do a whole word only, because I have names like `PK_Applicant'.

Is there a known method or tool I can use for this onerous task?


texts: PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

texts:
I have the following PostgreSQL schema:

CREATE TABLE User (
    ID INTEGER PRIMARY KEY
);

CREATE TABLE BOX (
    ID INTEGER PRIMARY KEY
);

CREATE SEQUENCE seq_item;

CREATE TABLE Item (
    ID INTEGER PRIMARY KEY DEFAULT nextval('seq_item'),
    SENDER INTEGER REFERENCES User(id),
    RECEIVER INTEGER REFERENCES User(id),
    INFO TEXT,
    BOX_ID INTEGER REFERENCES Box(id) NOT NULL,
    ARRIVAL TIMESTAMP
);

Its main use case is a typical producer/consumer scenario. Different users may insert an item in the database in a particular box for a particular user and each user can retrieve the topmost(this means the oldest) item in a box that is addressed to her/him. It more or less mimics the functionality of a queue on a database level.

More precisely, the most common operations are the following:

INSERT INTO ITEM(SENDER, RECEIVER, INFO, BOX_ID, ARRIVAL)
VALUES (nsid, nrid, ncontent, nqid, ntime);

And retrieve commands based on a combination of either RECEIVER+SENDER or RECEIVER+BOX_ID:

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.BOX_ID=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

and

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.SENDER=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

The last two snippets are packed within a stored procedure.

I was wondering how to achieve best performance given this use case and knowing that the users will insert and retrieve somewhere between 50,000 and 500,000 items (however, the database is never expected to contain more than 100,000 items at a given point)?

EDIT

This is the EXPLAIN I get with for the SELECT statements no indexes:

Limit (cost=23.07..23.07 rows=1 width=35)
   -> Sort (cost=23.07..25.07 rows=799 width=35)
      Sort Key: ARRIVAL
      -> Seq Scan on Item i (cost=0.00..19.07 rows=799 width=35)
         Filter: (((RECEIVER = 1) OR (RECEIVER IS NULL)) AND (SENDER = 1))

The best EXPLAIN I get based on my understanding is when I put an index on the time(CREATE INDEX ind ON Item(ARRIVAL);):

Limit (cost=0.42..2.88 rows=1 width=35)
   -> Index Scan using ti on Item i (cost=0.42..5899.42 rows=2397 width=35)
      Filter: (((receiver = 2) OR (RECEIVER IS NULL)) AND (SENDER = 2))

In all of the cases without index on ARRIVAL I have to sort the table which seems to my inefficient. If I try to combine an index on ARRIVAL and RECEIVER/SENDER I get the same explanation, but slightly slower.

Is it correct to assume that a single index on ARRIVAL is the most efficient option?


texts: Tuning Java Pool after getting ORA-04031

texts:
I'm running Oracle 12.2.0.1 Standard edition on Windows 2012 RC2.
I'm NOT running Oracle's Automatic Memory Management.

We are seeing these in the alert log:

ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","prv//////LBKLAGIMAAAAAAAA","JOXLE^aff7380e",":SGAClass")

First thing I thought to try was flushing the shared pool to resolve the immediate problem, while the dev team is fixing the SQL code, so I ran:

ALTER SYSTEM FLUSH SHARED_POOL;

Only then did I realise that the Java Pool isn't actually part of the shared pool. So I tried to take a look at the Java pool.

I think this tells me that my Java pool is roughly 32MB:

SQL> select * from v$javapool;

CATEGORY                                            MEMUSED     CON_ID
------------------------------------------------ ---------- ----------
:Intern                                              973440          0
:SGAClass                                          32103240          0
:SROState                                             81120          0

I ran this tuning tool, which if I can interpret correctly, says that there's not much benefit from increasing the Java pool size:

    select
       estd_lc_size,
       java_pool_size_for_estimate,
       java_pool_size_factor
    from
       v$java_pool_advice;

ESTD_LC_SIZE JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR
------------ --------------------------- ---------------------
           8                          16                    .5
          24                          32                     1
          25                          48                   1.5
          25                          64                     2
          25                          80                   2.5
          25                          96                     3
          25                         112                   3.5
          25                         128                     4
          25                         144                   4.5
          25                         160                     5
          25                         176                   5.5

I ran the following query taken from the Oracle docs here and it seems to suggest that the SGA_TARGET value is fine!

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

SGA_TARGET
----------
5167382528 = 4928 MB i.e. same as what it currently is!

I can see that:

    MEMORY_TARGET = 0
    SGA_MAX_SIZE = 4928M
    SGA_TARGET = 4928M
    JAVA_POOL_SIZE = 0
    SHARED_POOL_SIZE = 0
    DB_CACHE_SIZE = 0
    DB_KEEP_CACHE_SIZE = 0
    MEMORY_MAX_TARGET = 0

...so I'm bit confused how to tune this when we're not using AMM.

So how to resolve this issue?

is my best option to increase the Java pool size?
if so, how to do this when not using AMM?


texts: MySQL running fine while data files (disk) are not present. Data folder nowhere to be found

texts:
I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path.
The installer for some reasons never gave the option back to change the directory upon installation, despite multiple restarts.

I have re-installed MySQL anyway, stopped the service, deleted the "data" folder, and tried changing basedir through my.ini. It never worked. When I type "SHOW VARIABLES WHERE Variable_Name LIKE "%dir"" through SQL it returns that the data folder (datadir) is still "C:\ProgramData\MySQL\MySQL Server 5.6\Data\".

I know I shouldn't have deleted the data folder, but I figured they would create a fresh new DB when I delete it.

What's weird is that my database is working probably through some sort of cache.
I backed up my database through phpmyadmin, and restarted the server machine to see if the database is gone because the data folder is missing.
Nope, I was wrong. The database is still there with no data loss! And the data folder is still missing!

How is this even possible?
Where is the database being stored right now then?. How could I force MySQL server to write what's in the cache into the disk?


texts: create trigger which inserts rows depending on enum values?

texts:
I'm quite new to triggers and I ran into general problems. I'd like to achieve this:

CREATE TABLE `searcharticles` (
 `articleID` int(11) unsigned NOT NULL,
 `ean` char(13) COLLATE utf8_unicode_ci DEFAULT NULL,
 `manufacturerNumber` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`articleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `addresses` (
 `articleID` int(11) unsigned NOT NULL,
 `shop` enum('shop1','shop2') COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(255) COLLATE utf8_unicode_ci NULL,
 `status` tinyint(3) NOT NULL DEFAULT '0',
 `createTime` datetime NOT NULL,
 `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`articleID`,`shop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

When a new row is inserted in searcharticles, the table addresses should be filled, but not only once. I'd like to make a record for every shop that is in enum shop (I know this is not well designed). I'm stuck at this:

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
  INSERT INTO addresses(articleID, shop, createtime) VALUES(NEW.articleID, ?, CURRENT_TIMESTAMP);
END

How could I iterate through the possible enum values to trigger an insert with 'shop1' and another with 'shop2' without writing a possible shop name in the insert?