texts:
We have a Postgresql 9.5 running on CentOS release 6.5 (Final)
The server has 16GB of ram.
We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides this transaction no query is running on the DB in this stage but it can be run during other queries so it must be transactioned.

It works fine until a point where the OOM killer decides it's enough and kills the postmaster process:

Out of memory: Kill process 1766 (postmaster) score 890 or sacrifice child
Killed process 1766, UID 26, (postmaster) total-vm:24384508kB, anon-rss:14376288kB, file-rss:138616kB

Here are the relevant postgres configurations:

#-------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#-------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB                  # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
                                        # use none to disable dynamic shared memory

# - Disk -

#temp_file_limit = -1                   # limits per-session temp file space
                                        # in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0                  # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1           # 1-1000; 0 disables prefetching
#max_worker_processes = 8

Also the vm.overcommit_memory is set to 2

One solution I found is to disable the OOM killer for the specific process which I'm trying to avoid since it feels to me like not the correct solution.
I didn't find any way to force postgres to flush the memory to disk or to limit the total amount of memory used.

Any suggestions?


texts: PERCENT_RANK does not distribute over 100

texts:
I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way:

select Term, Frequency, percent_rank() over (order by Frequency desc) * 100
from Words

But when I look at the results, instead of seeing a column that starts from 0 and goes up to 100, I see a column that starts from 0 and goes up to 37.xxxx.

Though BOL does not explicitly mention that the result is distributed over 0-100 scale, my understanding from the word percent made me use this ranking function.

What do I miss here?


texts: Partition Switch - What is the benefit if staging has to be same as destination table

texts:
I am exploring to load large datasets into destination tables using SSIS.
I came across "Partition Switching" as one of methods to load data faster into destination tables.

But since the staging table has to be of the almost same configuration as destination table partition in order for partition switching to work, what is the real benefit here?

My concern is loading of the data after the cleanup/transformations. If staging table is exactly same as destination partition, loading time should be same for both. Or is it that loading data into a huge partitioned clustered indexed table will take more time versus loading into exactly same empty un-partitioned clustered indexed table?

We still have to fill up the staging table (which is almost same as destination), it's going to take the same time as to load data directly in destination partition, right? Why not load directly into destination then?

What is real benefit in terms of time to load the data here?


texts: reuse of temp table getting more time to execute

texts:
i'm creating temp table as,

SELECT
    T.CD,
    T.OBJECT_CD,
    T.PRICE
    INTO #TEMP FROM TABLE_NAME T

EDIT : ADDED CLUSTERED INDEX TO TEMP TABLE

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD
    ON #Temp (OBJECT_CD);

and using this table #TEMP two times in another query to get results as,

SELECT MAX(T1.PRICE),
        MIN(T1.PRICE),
        MAX(T2.PRICE),
        MIN(T2.PRICE)
        FROM PP INNER JOIN #TEMP T1 ON T1.OBJECT_CD = PP.OBJECT_CD
        INNER JOIN #TEMP T2 ON T2.CD = PP.CD AND T2.OBJECT_CD = PP.OBJECT_CD

but the query is taking more time to execute. when i remove any one of T1 or T2 from JOIN the query retrieves result instantly. what could be the reason?


texts: SSAS / MDX Query - Add an extra dimension when using Order

texts:
I wish to add an extra dimension to this query, so that I'm able to see the total amount of an item sold, per month/year:

SELECT
 NON EMPTY
 Order([Item].Children, Measures.[Total Line Value], DESC) ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

What I am looking for is something like so:

SELECT
 NON EMPTY
 Order([Item].Children, Measures.[Total Line Value], DESC), [January 2010].Children ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

This will eventually allow me to see the total sales of items for a particular month and year, but this throws an error as I'm not sure how to achieve it.

EDIT:

To clarify. The first query above works. It returns the grand total of items sold in order from highest to lowest BUT its for all time. I wish to restrict this to just show the order from highest to lower for just 1 month only, that being January 2010. I have a time field in my cube where it's possible to select the month and year like so: [January 2010].Children - I'm able to view this in excel, I just need to be able to view this in my application.

I have a similar query, this one returns all the sales, for each day of January 2010:

SELECT
NON EMPTY   { [January 2010].Children } ON COLUMNS,
            { [Total Line Value] } ON ROWS
FROM        [Sales Analysis]

I just want to split this down further to show each individual item's sales for that month, in order from highest to lowest.


texts:
I based my account/user table design following this article. So I have set my table relationships to look something like this:

User <- Memberhip -> Group -> Account

With this design, a user is tied to group via a membership table. A group belongs to an account. so far so good.

Now I want to add a new resource catalog that belongs to an account, but can be accessed by a group. A group may have different access levels to different catalogs; it may have full edit access to one catalog, but read-only to a different one. A catalog may be accessed to by different groups. Since this is a many-to-many relationship, a pivot table is required which will also determine the groups access level to each catalog:

I tried putting the catalog table directly under a group, but with this approach, the account is not the direct owner of the catalog but the group is. its harder to give access to different groups. I could also create new groups for every new catalog, but then existing groups can belong to only one catalog.

How can I fix this design so that is no circular relation in it?


texts: How to make sure a record is only retrieved once

texts:
If I have a table called Coupons that contains 10,000 records. How should I design the schema or what query should I use so that my API never gets the same record twice. If I simply ask the DB for last record I think it's possible another instance could ask the DB at the same time for that record.

Should I look into record locking ? Are there any performance implications with this? The table is going to be accessed a lot. Since there are 10,000 records it's ok if some records are locked since the DB can return a record that isn't locked.

Coupon table so far is just

id [int]
coupon [varchar]
project [int](fk to projects table)

Mysql 5.6

Edit: I am just serving coupons. As soon as I pull from DB it is considered used to me. I don't have an ID to look up either. I want to literally recreate that ticket machine at the DMV that puts people in order of who to be served. Once a number is pulled no one else will get that number. I'm concerned if code tries to SELECT last coupon simultaneously and gives out same number twice.


texts: Why is this frequent SQL not in the SQL Server procedure cache?

texts:
I frequently query the SQL Server procedure cache (dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan) to get execution stats and execution plans.

We have a new application (with a new development team, etc.) and none of its SQL is showing up in the procedure cache.

The cache has SQL from Idera and from internal SQL Server processes;
Idera is reporting some long-running SQL from the application;
I can see application SQL in the sessions in the activity monitor.

But nothing from this application in the procedure cache. So I am almost blind here.
This is SQL Server 2017 - but I do not have this issue with other SQL Server 2017 databases.
This application is .Net (using Core .Net SqlClient Data Provider to connect) and this is a bit of an oddity for us, as most of our applications use Java, Tomcat and JDBC.

I have seen articles saying that RECOMPILE hints/options will not save the SQL in the procedure cache. I don't think the dev team is deliberately doing that. Is it possible that there is a setting in the provider that is forcing that?

Can anyone suggest avenues of investigation for this?


texts: List partitioning or filtering a non-partitioned table, which incurs lower cost?

texts:
Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed.

Set 1:

INSERT INTO PARTITIONED__SOURCE_TBL VALUES ...;
SELECT * FROM PARTITIONED__SOURCE_TBL WHERE period = <some value>;

  Total cost = [Cost to insert rows into partitioned table] + [Cost to pull data from a specific partition]

'Cost to insert rows into partitioned table': Would this be higher for a non-partitioned table?

'Cost to pull data from a specific partition': Should be minimal as you are basically pulling data from a logical table rather than using an index to filter out the records

Set 2:

INSERT INTO SOURCE_TBL VALUES ...;
SELECT * FROM SOURCE_TBL WHERE period = <some value>;

  Total cost = [Cost to insert into non-partitioned table] + [Cost to perform a full table scan, filter data row-wise and retrieve a required data ]

'Cost to perform a full table scan, filter data row-wise and retrieve a required data': I feel this would be higher than pulling data from a partition as we are pulling data from a single partition at a time, so Oracle needs to look at a single partition (subset of the table).

Now as both above sets return the same required data.. which approach is considered better..?
As @Phil has mentioned below, a good indexed table should be better than having a partition for better performance. Note that we already have the license for partitioning so that is not a factor here.


texts: Slow query while selecting a higher offset from millions of rows

texts:
I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields including

id for primary key

log_time index

and other 8 columns.

When I perform a select query sorted by log_time with offset of some million, the query gets a lots of time to process.

My Query:

SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;

Explain

mysql> explain SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | securityData | ALL  | NULL          | NULL | NULL    | NULL | 2664456 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+

Table Details

mysql> explain securityData;

+-----------+-----------------------+------+-----+---------+----------------+
| Field     | Type                  | Null | Key | Default | Extra          |
+-----------+-----------------------+------+-----+---------+----------------+
| id        | bigint(20)            | NO   | PRI | NULL    | auto_increment |
| log_time  | double(18,6) unsigned | NO   | MUL | NULL    |                |
/// more fields
+-----------+-----------------------+------+-----+---------+----------------+

The time of processing go higher and higher when I increase the offset value. I tried in both MyISAM and INNODB

How could I achieve the performance of this query?


texts: Export Data Pump all data from few schemas except one table structure only?

texts:
we use to make a copy of schemas from one database and move it to another for testing using this par file ...

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
EXCLUDE=TABLE:"IN ('SYS_HISTORY')"

then create the CONFIG_HISTORY with a script on the target database!

is there any way to NOT EXCLUDE=TABLE:"IN ('SYS_HISTORY')" and export the structure only of this table?

can I use something like this:

INCLUDE=TABLE:"IN ('SYS_HISTORY')"
QUERY=SYS_HISTORY:"where CON_ID < 0"

note: there is no CON_ID with value less than 0, it will return nothing when CON_ID < 0!


texts: Drawing an entity-relationship diagram for a store business context

texts:
I have an entity called Users, with the attributes Id, Name, and Phone No. The objective is to develop an entity-relationship diagram (ERD for brevity) where Users might be Store Cashiers, and also Customers.

Cashiers have the attributes Position and Work Hours.
Customers have the attributes Credit History and Amount.
A Cashier can also be a Customer.
Only a Cashier can add a Customer to the Store's database.

The question

It is easy to develop the ERD where I can include Roles, but then how can I enforce the following condition in the ERD?

Only a Cashier can add a Customer to the Store's database.


texts: Index re/organize/Rebuild Report using Ola

texts:
Is there any way we can generate report after running IndexOptimize - USER_DATABASES jobs.

I need a report which tells me which indexes have been rebuild/re-organised.

    Select * from commandlog

I had a look in the above table, but I am unable to identify which indexes has this actually performed rebuild/re-organize. Even though there is a command logged in the table, there are always few tables which didn't get rebuild/reorganized for different reasons like pagecounts etc..

Does anyone had any requirement like this. Can anyone share any script with me please.


texts: Cannot install Oracle Grid on Oracle Linux 6.4

texts:
I have an Oracle database 11gr2 and I want to install Oracle Grid on Oracle Linux 6.4.
At first, I have installed Oracle Grid with the tutorial on this website:

https://eudba.wordpress.com/2012/08/10/installing-oracle-11g-on-oracle-linux-6-with-asm-eng/

When the grid installation is finished, I have an error that says: "the Oracle grid service can not start".

When I want to install the database with oracle user and with ASM, I have an error that I attached it to this question.

Can any one help me?


texts: Hard limitation for merge replication article count?

texts:
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article count limit. (Using SQL Standard 2005 at the server, and 2005 Express at the subscriber level.)

We are in the process of developing a new version, with a new database built from the ground up, which will have a minimum system requirement of SQL 2008 R2 Standard server side and Express 2008 R2 at the client end, which also appears to have the same 256 count limit on published articles. (yes, we will also support 2012, it's limits are the same though)

The question comes in where we have successfully test published out over 400 articles in the new version, and everything tests functionally (pub creation, snapshot and subscriber creation, and replication itself). Has anyone else exceeded the count limit before, and is this just a soft recommended limit? We have not seen any errors, either during publication creation, or subscriber creation.

According to this article: http://msdn.microsoft.com/en-us/library/ms143432%28v=sql.105%29

I'm waiting on a call back from Microsoft, but I'd like to know if anyone has seen this before, in any ver of SQL.


texts:
We have a MariaDB Galera Setup, so do we require primary keys in a MySQL view?

It contains a complex select query with joins of tables and the values get updated. so do they replicate in a Galera DB setup properly or do we need to create primary Key for the views as well. Can anyone explain what's the behaviour of the Views in a Galera cluster environment.


texts: How to implement a multi-user account facility in a database

texts:
I'm modelling a database schema that has the requirement of including a multi-user account facility for premium customers:

  A multiple account enables multiple users to login to the same account
  in order to do the job according to their permission level. This means
  that it is possible to maintain a single account for your entire
  marketing team. The account owner can add as many users as he/she
  wants and limit what permissions each of them has. You can choose role
  for each user: account manager, marketing executive, marketing
  assistant, etc… or create a customized permission.

At the moment I have one users table that stores the information of a generic user with the username and password etc. How would one implement a multi-user account facility? Could an extra field be added to the users table? Would a separate table be required? I would like to use the principle of keeping this simple where possible.


texts: Should MySQL database be in dedicated server?

texts:
The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated server.

We have been experiencing application slowing down lately but when we check (after the users done for the day of course), everything works super fast. We have done load testing and everything seems fine which make me wonder if the lack of dedicated server takes up too much of CPU.


texts: What changes SQL Server Compatibility Level changes?

texts:
We had a database and lots of functionality that worked fine in SQL Server 2008 R2.  We recently upgraded both our development and production servers to SQL Server 2014.  Unbeknownst to us, our development server stayed in compatibility mode for 2008 R2, but the Prod box is in Compatibility Mode for 2014.

My question, is where can I find what changed between SQL/Compatibility versions so we will know what needs fixing to maintain functionality.  I know I can set (or have set) the Prod server to compatibility with 2008 but since I don't administer the prod box, I cannot make that call.  My google-fu seems to keep turning up tutorials on how to change the compatibility level, but nothing on what actually changed so I can't tune our queries/procs/functions/etc to work well with the new version.


texts:
I have backed up database located on Host1 and restored/recovered it on Host2.
After a while, I backed up DB on Host1 again.

Now I want DBs on Host1 and Host2 be the same using last backup.

How can I achieve this? Without deleting database on Host2?


texts: ORA : 12519 No appropriate Service Handler

texts:
Sorry if my question has already been asked many times but I spent two days trying to solve it.

I have a machine in which I had an Oracle database. Everything was working propertly until I added two other databases.

Now, I can connect only to the last database created.

The three databases have the same connection settings (except the SID) :

Host : localhost
Port : 1521
SID : Database1, Database2, Database3 (the last one created)

My tnsnames.ora file is as follows :

DATABASE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE3)
    )
  )

DATABASE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE2)
    )
  )

DATABASE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE1)
    )
  )

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

My Listener.ora file is as follows :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE3)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\user

When connecting to the databases :

Database3 : Works fine !
Database2 : ORA 12505, TNS:listener does not currently know of SID given in connect descriptor.
Database1 : ORA 12519, TNS:no appropriate service handler found

I restarted the listener many times.

The command lsnrctl status returns (sorry it's in french)

Connection to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                07-JUIL.-2014 09:24:42
Activity                  0 jours 12 heures 45 min. 0 sec
Niveau de trace           off
Sécurité                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramétres du processus d'écoute      E:\app\user\product\11.2.0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'écoute           e:\app\user\diag\tnslsnr\MyComputer\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyComputer)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 2 gestionnaire(s) pour ce service...
Le service "Database1" comporte 1 instance(s).
  L'instance "Database1", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3XDB" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
La commande a réussi

I read in a forum that this might be a matter of connections maximum reached, I'm sure it's not the problem because no one is connected to my machine.

If any one can provide some help, I will be grateful.

Thanks.


texts: Insert the value of column in another column as jsonb

texts:
I'm trying to insert the value of one column as the value for another column of type jsonb, so this is what I currently have:

INSERT INTO "Customers" ("id", "priceList", "metadata")
SELECT
  id,
  priceList as Price,
  metadata as Others,
  json_build_object('id',id,'Price',Price,'Others',Others) as jsonField
FROM "CustomersCopel"

but it doesn't work as you can see what I want is add the results of id, priceList and metadata in the jsonb column called 'jsonField'

this is the version of postgres I'm running:
PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


texts: Materialized views use older dbms_job instead of scheduler?

texts:
Very new to this topic - using 11g.

When I create a materialized view, a matching job (not a scheduler job) is created.  But it's my understanding that dbms_job is being replaced by dbms_scheduler.

Am I doing something wrong or am I totally misunderstanding what's going on?


texts: Select records based on earliest date in a linked record

texts:
I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you!

The database's domain is fiction stories, and I have these tables (among others):

  Stories: | story_id | title | genre | ...

  Venues : | venue_id | name | year | ...

  Story_in_Venue: | story_id | venue_id |

The Story_in_Venue table implements a many-to-many relationship, indicating that a given story appeared in a particular venue (such as a magazine or anthology). One of the common searches is for all stories within a given genre (say 'mystery') in which the earliest venue is in a particular year (say 1960).

If I made a generated column in the Stories table to hold the year of a story's earliest venue, then the search would be quick and easy, going straight to the stories I need (provided that I have an appropriate composite index). But it's been drilled into me to not duplicate information that's already available in another table.

Is there a good way to solve this problem:

without that earliest year column in the Stories table (and no other generated or trigger-constrained column either), and
without having to examine all mystery stories or a bunch of irrelevant venues.

Many thanks for sharing your knowledge on this beginner's question.

--Michael


texts:
We have a few MySQL (InnoDB) tables with repeated data and I want to clean them up. Consider the following:

Table user_clubs

| id | user_id | name                   | function                 | description         | school_grade |
|----|---------|------------------------|--------------------------|---------------------|--------------|
| 1  | 10      | World Cultures Club    |                          | Cultural activities | Freshman     |
| 2  | 10      | World Cultures Club    |                          | Cultural activities | Sophomore    |
| 3  | 10      | World Cultures Club    |                          | Cultural activities | Senior       |
| 4  | 20      | National Honor Society |                          | Vounteer            | Second Year  |
| 5  | 30      | National Honor Society | Public Relations Officer | School Society      | 11th         |
| 6  | 30      | National Honor Society | Public Relations Officer | School Society      | 12th         |
| 7  | 40      | Student Council        | Sophomore Secretary      |                     | Sophomore    |
| 8  | 40      | Student Council        | Junior Secretary         |                     | Junior       |

We have duplicated rows for each school_grade. What I want to end up with is:

Remove the school_grade column, having all the school_grade values in a user_clubs_meta table
Remove unnecessary duplicates in the user_clubs table, keeping only the record which has a related user_clubs_meta record

Example:

record id 1,2 & 3 need to get reduced to a single row and a new user_clubs_meta row with meta_key=school_grades and meta_value=["Freshman","Sophomore","Senior"] needs to get added (id column omitted for simplicity)
id 4 can be left as-is
id's 5 & 6 needs to get consolidated & meta added
id's 7 & 8 can be left as-is

Where I need help:

Populate the user_clubs_meta table. I have the following query:

INSERT INTO user_clubs_meta (user_clubs_id, meta_key, meta_value)
SELECT id, 'school_grades', CONCAT('[', GROUP_CONCAT(CONCAT('"', school_grade, '"')), ']')
FROM user_clubs
GROUP BY user_id, name, function, description
HAVING COUNT(*) > 1;

Data looks good to me, but I'm not 100% positive if this gives me the correct result. If someone could confirm.
Remove the duplicate records from user_clubs. This is where I'm completely stuck. I guess basically do the opposite/diff of

SELECT id
FROM user_clubs
GROUP BY user_id, name, function, description
HAVING COUNT(*) > 1;

where the query would give me only a list of id's which I can remove from user_clubs unless the record has a related record in user_clubs_meta.

Any suggestion is highly appreciated.


texts: 'Can't create thread to handle new connection' on RDS

texts:
I have two production instance of MySQL on AWS RDS that failed with:

2017-06-20T15:48:35.226833Z 0 [ERROR] Error log throttle: 1 'Can't create thread to handle new connection' error(s) suppressed
2017-06-20T15:48:35.226873Z 0 [ERROR] Can't create thread to handle new connection(errno= 11)

These are two db.m4.2xlarge instances and one capped at 102 connections while the other capped at 38. Not more connections could be made past that. This type of instance should be able to accept more than 2K connections with the default config (which I have).

I opened a support ticket and they manually changed some settings on the instances. After that and a reboot, I could connect with 2500 connections on each server to validate resolution.

Any idea why they had to manually change some settings on the instance (underlying OS I guess)? Is this something to be expected from RDS? What could we be doing that triggered this condition?

Thanks!


texts:
I have a table in access linked to SQL.I am doing a query where I am using a field named comment from this table.
The field is designed in SQL as nvarchar(255) . I am writing integers numbers in this field. What I want to do in my query is to convert this field from nvarchar to int so I can use the SUM function.Can anyone help?


texts: to change collation for system database SQL Server 2017

texts:
We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change  all system databases (master , model , temp, msdb) to another collation. how to change this without installing SQL Server ?
thanks,


texts: Simplify SQL Query which aggregates per year

texts:
This query works, but uses TO_CHAR(date_created, 'YYYY') three times.

Is there a way to simplify this query?

SELECT TO_CHAR(date_created, 'YYYY'), count(*)
FROM ticket
WHERE date_created >= '2013-01-01' and
      date_created <= '2017-06-01'
GROUP BY TO_CHAR(date_created, 'YYYY')
ORDER BY TO_CHAR(date_created, 'YYYY');

Result:

 to_char | count
---------+--------
 2013    | 441500
 2014    | 574660
 2015    | 247910
 2016    | 601234

I want to simplify it. Performance does not matter at all (in this case). Unfortunately I found no matching tag for this question. I took "optimization".

I use PostgreSQL


texts: What is the difference between these extracting date method?

texts:
Assuming I have a table called MyTable, with a TimeStamp field "Created". I want only extract the Date part of this column, and in my research I found these alternatives:
SELECT created AS "Original",
       date(created) AS "DateFunction",
       created::date AS "DoubleColonCast",
       cast(created as date) AS "CastFunction",
       date_trunc('day', created) AS "DateTrunc"
FROM MyTable

In matter of SARGability, what is the preferred method?
What is the "SQL ANSI" (ie more portable) way?
What is the most common/used in Postgres?


texts: Import data into MySQL using Talend Open Studio

texts:
I have been trying to import CSV data about 60 MB; it contains about 400,000 rows, which should not be too much.  I tried these ways to import the data:

MySQL Workbench - it took about two hours!!!
Using MySQL LOAD INFILE command - it took about 15 seconds!
Using Talend Open Studio - almost as long as Workbench.

Why are the GUI tools working so badly?  I want to use GUI-based tools, instead of writing scripts.

With Talend Open Studio, is there a way to speed things up?  I heard that there is possibly another Talend product that deals with Big Data, will this do it faster than Talend Open Studio?

Hope someone out there knows!

I know that in Hadoop using Pig, I can get the the CSV from HDFS into Pig memory very quickly.


texts: How to prevent redundant data that is hard to detect, e.g. John Smith

texts:
I have a database table to store technicians (employees) which is structured like this:

| firstName | lastName | phone | alternatePhone | email | canadaTravel | canDrive | notes |

In this table, only the first two columns are required. Any other values can be null. I would like to prevent against duplicate employees, but I realize this key cannot be made based on name; There may be two John Smiths that we hire.

My initial thought was to expand a key to (first, last, phone) or (first, last, email) but because those are nullable fields, it is not guaranteed to be unique.

Is there a way to design the table to allow for two different people with the same name, but prevent the same person being entered twice?


texts: Select * on inner join with common columns

texts:
I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c as Cx ...) but this requires to list the columns. However it is not really an option. Is there a way I could give an alias to all the columns of a table at once?

Something like select a.* as a*?


texts: How can I tell how many Cores SQL Server is actually using?

texts:
I have two servers running SQL Server.

Server 1: SQL Server 2008 R2 Express (4 core)
Server 2: SQL Server 2012 Developer Edition (8 core)

As far as I am aware SQL Server 2008 R2 Express should only use one core.

SQL Server 2012 Developer version should use all 8 cores.

However if I run the following command inside of SQL query on SQL Server 2008 R2 Express it's showing 4 cores.

select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

Am I using the right command to gauge the usage?


texts:
I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise).  However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.

Here's what I've tried to skip execution if the PERIOD already exists on the table.  All of these raise the same error:

  Msg 13597, Level 16, State 2, Line xx

  Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.

Only add the PERIOD if it doesn't currently exist:

if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

This raised the error.
Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:

if 1=2
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

But that raised the error as well.
Use SET NOEXEC ON to stop execution of the code:

set noexec ON
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
set noexec OFF

Didn't work.  The ALTER TABLE still raised the error.
Admit defeat and wrap it in a TRY/CATCH and just ignore the error:

begin try
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
end try
begin catch
end catch

This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.

Is this a bug in Azure SQL Server?  This is running on a Managed Instance.  The @@VERSION reports:

  Microsoft SQL Azure (RTM) - 12.0.2000.8   Nov  2 2018 21:17:06   Copyright (C) 2018 Microsoft Corporation

UPDATE:

I can use a similar technique to skip adding columns that already exist without any issues:

if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
GO

The above code only executes if the sysjobhistory does not contain the column StartTime.  If it already does, the code does nothing and continues executing.  The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.


texts: Postgresql 9.6 best settings for large admin work (min_wal and max_wal)

texts:
We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a pg_restore of a simple database dump of less than 10 tables, no binary data or blobs, just simple text (a comments system). But one table has about 200GB of data, so it's large.

There's no other jobs for this DB. Only this maintenance task. What are the best settings for this purpose given the config above? PGSQL's documentation does not help me too much. My specific question is around wal settings.

If we're OK with using the whole of this server just to do a pg_restore, and nothing else other than PG is on this server, what settings should we use? This is what we have now:

maintenance_work_mem = 1500MB
fsync = off
synchronous_commit = off
wal_level = minimal
full_page_writes = off
wal_buffers = 64MB
#-----  checkpoint_segments = 512
#-----  max_wal_size = (3 * checkpoint_segments) * 16MB
#-- min_wal_size = 100MB    # 80MB is the default
max_wal_size = 24576MB   # based on 512 checkpoint_segments
max_wal_senders = 0
wal_keep_segments = 0
archive_mode = off
autovacuum = off

Note that using top, we find that memory is not the issue. The CPU cores are spiking to 100 or so, and then dipping. This is an intensive write process, so that makes sense. Welcome any simple-to-understand guidance on how the min_wal_size should be set -- note that it's commented for us now.


texts:
I have a rather simple update/query, that has been causing me a lot of grief over the years.

in simplest form it is :

update VillageSemaphore
set TimeStamp = getdate()
        where VillageID in (@X, @Y)

However, in some stored procs, the query also include this "OR VillageID in (...)" subquery

update VillageSemaphore
set TimeStamp = getdate()
        where VillageID in (@X, @Y)

        OR VillageID in  ( -- this subquery can return many rows, many different VillageIDs
        select VSU.SupportingVillageID
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z
            and VSU.UnitCount <> 0
            )

Note that this OR, can return many villageIDs, not just one, @Z. This version of the query, sometimes runs a very long time. No index rebuild, stats rebuild helps. It runs slowly when the contents of Villages table is deleted and repopulated. In this case, the row count would be just a few hundred rows. I never figured out why this is, and always just lived with it.

However, recently I was looking at the query plan:

It seems that estimated number of rows (4000) is huge in comparison with the actual number of rows (2).

I created this statistic but it does not help

CREATE STATISTICS [stat_x] ON [VillageSU]([UnitCount], [VillageID])

SO MY QUESTION : any suggestions why this could be and what I could do to improve this ?

for reference, the table looks like this :

CREATE TABLE VillageSemaphore(
    VillageID    int         NOT NULL,
    TimeStamp    datetime    NOT NULL,
    CONSTRAINT PK97 PRIMARY KEY CLUSTERED (VillageID)
)

UPDATE: Trying out this version of the query as suggested by srutzky

CREATE TABLE #VillagesToLock (VillageID INT NOT NULL);
insert into #VillagesToLock values (@X)
insert into #VillagesToLock values (@Y)
insert into #VillagesToLock select VSU.SupportingVillageID
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z
            and VSU.UnitCount <> 0

update VillageSemaphore set TimeStamp = getdate()
    where VillageID in (select VillageID from #VillagesToLock)

this is the result so far : http://screencast.com/t/96KafTPoNGM - query plan does look better.

The cost of the query went down from 3% to 1% also, so that seems good. 3% may not seem like much, but this is a 2500 line stored proc!

QUESTION: I cannot make #VillagesToLock.VillageID a PK since it is not unique. And I expect the #VillagesToLock to be typically no more than 2-10 rows. VillageSemaphore  could be many thousands of rows. Is it worth putting an index on #VillagesToLock in this case ?

UPDATE NOV 24
I have implemented this alternative

The query plan does look a lot nicer

Thank you to all who too the time to help me out!


texts: Exactly same query and plan but different duration and total reads. I Know parameter sniffing but

texts:
I know what is parameter sniffing but this seems different, I have this simple query:

select * from A400RDATA

This table has only a cluster index and approximately 25000 rows. The query usually takes 1 second but now It took 20 seconds.

I found its execution plan in the cache and I used sp_BlitzCache (thanks Brent Ozar) for analyze every details -> It's true, the last execution takes 20 seconds.

So I run this query again with a little change, a leading space: "select * from A400RDATA" (note leading space before s of select). The leading space is a change in the query text so SQL Optimizer generates a new query plan.

The two query plans are identical, they are trivial plans and they just use the cluster index, but the total reads and duration are different.
Following the two different BlitzCache results:

Solarwind shows a drop in Page Life Expectancy, and SQL Server instance uses only one core (MAXDOP = 1).
I think MAXDOP and PLE can be the cause of the issue, what do you think ?
How can I be sure of that?
How can I know if the data was taken from disk or RAM?

I checked the logical and physical reads of the 20 seconds execution plan:

Execution time: 3
Physical Reads: 0
Rows Processed: 76000
Logical Writes: 0
Logical Reads: 3841

No physical reads.......
So PLE isn't the cause (perhaps).
I have read again the plan's details: 3 executions and 20 seconds of total duration, so each execution took 6 seconds but relatively little CPU time:

Total Duration (ms) = 20,013.84
Total CPU (ms) = 192.57

Can be CPU the problem? Maybe only one CPU is not enough and SQL brings the query in RUNNABLE state (waiting for CPU time).


texts:
I have a simple relational MySQL database with users, cars an a log table. Everything works fine. In the log table I want to 'register' the users of a car. But a car can have two users at the same time.

My question: How can I show multiple users in the log table, using the relational database?

My database structure:

Table users

user_id  name   phone
-------  -----  -----
1        john   123
2        kevin  456

Table cars

car_id  nr   brand
------  ---  -----
1       666  Ford
2       999  Dodge

Table log

log_id  car_id  user_id1  user_id2
------  ------  --------  --------
1       1       1         2

The car.car_id is related to log.car_id and users.user_id is related to log.user_id1 and log.user_id2.

Supposed SQL:

SELECT log.*, cars.*, users.*
FROM log
LEFT JOIN cars ON log.car_id=car.car_id
LEFT JOIN users ON log.user_id1=users.user_id
LEFT JOIN users ON log.user_id2=users.user_id

PHP (in loop of course):

echo $row["brand"];
echo $row["name"]; // user_id1
echo $row["name"]; // user_id2

It's working for only one name. How to show the names of user_id1 and user_id2 in the same "log" row?


texts: DB user restricted permissions vs Docker?

texts:
As far as I know, it's usually considered a good practice when developing websites to store in website config DB credentials of the user without ALTER/DROP permissions (so user may only select, insert, delete rows, but cannot modify DB structure).

But nowadays more and more sites using Docker for deployment and usually DB images like mysql:latest grant all permissions for the given database to the given user.

So my question is - should I manually go to MySQL DB as root during deployment and restrict the permissions or should I just keep it as is?


texts:
Consider the following config

shared_buffers = 20GB
max_wal_size = 2GB
min_wal_size = 1GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

and checkpoints' log

2020-05-25 16:28:37.128 | LOG:  checkpoint starting: time
2020-05-25 16:32:07.098 | LOG:  checkpoint complete: wrote 21420 buffers (0.8%); 0 WAL file(s) added, 0 removed, 7 recycled; write=209.932 s, sync=0.009 s, total=209.969 s; sync files=295, longest=0.002 s, average=0.000 s; distance=45191 kB, estimate=503334 kB
2020-05-25 16:33:37.165 | LOG:  checkpoint starting: time
2020-05-25 16:37:08.001 | LOG:  checkpoint complete: wrote 25041 buffers (1.0%); 0 WAL file(s) added, 0 removed, 25 recycled; write=209.842 s, sync=0.865 s, total=210.835 s; sync files=421, longest=0.120 s, average=0.002 s; distance=82180 kB, estimate=461218 kB
2020-05-25 16:38:02.690 | LOG:  checkpoint starting: xlog
2020-05-25 16:41:32.181 | LOG:  checkpoint complete: wrote 191629 buffers (7.3%); 0 WAL file(s) added, 0 removed, 42 recycled; write=209.239 s, sync=0.049 s, total=209.491 s; sync files=666, longest=0.006 s, average=0.000 s; distance=758070 kB, estimate=758070 kB

Why at 16:41 the xlog checkpoint was started? The distance from prev checkpoint is just 758070 KB and it's greatly less then max_wal_size.

PS

It seems that max_wal_size is spread between several checkpoints, and xlog checkpoint is started if value max_wal_size / (2 + checkpoint_completion_target ) is exceeded. Am I right?


texts: "Warning: The join order has been enforced because a local join hint is used" returned with no Join hints

texts:
I am running the below (homemade) analysis query, looking at wait states, memory usage, and other info for queries running on the current server.

Select GetDate() as RunDate,
    spid,
    blocked,
    waittype,
    lastwaittype,
    waittime,
    sp.dbid,
    uid,
    status,
    hostname,
    program_name,
    cmd,
    nt_domain,
    nt_username,
    loginame,
    sp.sql_handle,
    stmt_start,
    stmt_end,
    session_id,
    dop,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    query_cost,
    ideal_memory_kb
    ,SUBSTRING (qt.text, stmt_start/2+1, (
        CASE
            WHEN stmt_end = -1
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 + 1
            ELSE stmt_end
        END - stmt_start)/2) AS [Individual Query]
    ,[Parent Query] = qt.text

FROM sys.dm_exec_query_memory_grants mg
INNER JOIN sys.sysprocesses sp
    ON mg.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as qt
WHERE sp.spid != @@SPID
ORDER BY requested_memory_kb

When I run this and there are no currently running queries, I receive the warning message:
    Warning: The join order has been enforced because a local join hint is used
As you can see, there are no join hints in the above code. This is only annoying in my context, but is this an annoying SQL Server bug, is this a known thing, or is it something else?

SQL version: Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Running on VMWare. Running in SSMS 2016 (Version 13.0.16106.4)


texts: Apply the latest Service Pack and Cumulative Update

texts: I have given an RTM version of SQL Server 2014 and I need to apply the latest SP (currently SP2) and CU (currently CU8). Do I need to apply SP1 first? Or can I straight move to SQL Server 2014 SP2 CU8 without applying the old Service Pack 1 and the related Cumulative updates?


texts: Large Logfile full but very small tlog backup

texts:
We have a SQL Server 2014 SP2 instance, where I discovered a strange behavior. One of the databases (Full recovery model) has a log file of approx 10GB and I see regular file growths at random times.

I haven't yet found out what causes the file growths, but what intrigues me is that the log file is empty in the morning and the log backup (taken every 15min) is most of the time 700KB and one time 12MB.

Can somebody explain how a full log file of 10GB can generate such a small log backups? Can backup compression do that?

Because the log growths happen at night, I wasn't able to execute DBCC SQLPERF(LOGSPACE), but I expect that the file was full, otherwise it wouldn't have grown. Likewise, I cannot say what the log_reuse_wait_desc was.

I have now setup monitoring so I can obtain extra data.


texts: Retrieving executed query list in BigQuery via SQL

texts: Is there a table similar to v$sql in Oracle where I can retrieve data associated with a particular query that was run in BigQuery using SQL?


texts: some SQL Server MSX targets show as Normal, Blocked

texts:
I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window Manage Target Servers, instead of all instances showing as Normal, they show as Normal, Blocked. I have failed to figure out why this happens. Defecting and re-enlisting usually cures this ill, but that means having to re-add that target in any appropriate jobs, so I would rather resolve the cause than endure the workaround.

Any advice? I would like to keep using SQL Server's tools for multi-server administration, but am open to other avenues.


texts: What MDX can and SQL Can't perform on the same datatbase

texts: Can anyone explain or show me example(s) what MDX can do and SQL can not do on same database? please...


texts: TNS-12543: TNS:destination host unreachable

texts:
I have a installed oracle 19c
but i am unable to login

Please refer the following screen shot

I am not unable to login
which user credential require to login


texts: How to store multiple vendor data in Google Cloud SQL?

texts:
I am designing a mobile app in which I need to host my vendor data and give access to that data after analysis to only the specified users. Since I will have multiple vendors with different set of data and users, should I have multiple database 1 of each vendor or 1 database with different schema ? I plan to run the application on Google Cloud Platform and use Google Cloud SQL.

What are advantaged and disadvantages of using single database to host multiple vendor data?


texts: "SQLNCLI11" for linked server "linkedserver" returned message "The transaction manager has disabled its support for remote/network transactions."

texts:
I have a database in a SQL Server 2014 AG group (AG1).- two nodes.

On both nodes, a linked server is set up.  I am selecting data from a table from AG group (AG1) database and inserting into a linked server (a database from another AG group(AG2)).  The only statement in the transaction is an insert statement.  It is not a distributed transaction.

Why do i get the following error?

  OLE DB provider "SQLNCLI11" for linked server "LinkedserverListener" returned message "The transaction manager has disabled its support for remote/network transactions.".
  Msg 7391, Level 16, State 2, Line 2
  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "LinkedServerListener" was unable to begin a distributed transaction.

Sample code is a below.

Drop table #Myatran
Select 'ABC' name into #Myatran

DECLARE @pndstop_ps NVARCHAR(4000)
SET @pndstop_ps =
N'
INSERT INTO '+ 'linkedserver'+'.DB.dbo.MyaTEST(Name) SELECT name FROM #Myatran
'
SET XACT_ABORT ON
BEGIN TRANSACTION

EXEC sp_executeSQl @pndstop_ps

SET XACT_ABORT OFF


texts:
I have a database which is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction, since either all or no data should be inserted. Currently the values are inserted one at a time. It takes a total of about 37 seconds for 200 inserts before the commit.

The problem is that there may be 10 sites continuously trying to insert data within a 2 minute interval. If they fail (due to concurrency errors) they will retry until they succeed. This adds up to an impossible situation as they will start growing a backlog.

I understand I can use a snapshot isolation level to allow reads in parallel, but how should I handle the parallel insets? How do banks and other systems do it since they should also get a backlog if they do it in a queue?

I use SQL Server and on the remote client I use C# to talk to the SQL Server.

The inserts are of 10 different types and are they are linked in pairs of two. The inserts are connected in pairs. The first insert is a generic one which is the same for all and are inserted into the same table - let's call it table_A.
Then the second insert is specific depending on the type of insert data. The second insert is made to 1 of 10 tables and the data varies greatly but that table also store the row index of the first insert in table_A. This is how they are linked.

The transfer speed from a good connection is about 1 second and that is no problem. The problem is the remote part that takes about 40 seconds. Table_A contains about 10 million rows and is well indexed. All other tables are somewhat smaller and well indexed.


texts: Sequence number for COMMITTED concurrent inserts

texts:
For example, I have a table

create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type

and many concurrent inserts into this table. Is there is a way to fill the OrderNum column with data that will show the order in which a row was committed in this table? Not inserted!

I want to realize many Queue readers, which will periodically select new rows where OrderNum > (max(OrderNum) of the previous select from Queue). In this case I can't use either identity or rowversion, because they are generated in the INSERT statement and the reader can select a committed row with OrderNum = 2 while a row with OrderNum = 1 still isn't committed. So we would have a gap, or data loss for readers.


texts: How to optimize constant querying for a sum of many rows?

texts:
I'm making an app that is supposed to view election results (yep, homework assignment). For each of the 24681 wards and each of the 12 candidates the number of votes the said candidate has received in the said ward is to be stored.

The most common operation, of course, would be viewing voting results aggregated over all election wards. Also there must be separate tables for each province, electoral district and municipality. Viewing aggregate voting results for each of these areas must also be possible.

Aggregating election results over the whole country takes up time measured in seconds. I consider this inappropriate even despite it's only a homework assignment. If this app was to be exposed to general public it would be unusable. I think it is broken to require aggregating results over 24681 wards each time a user loads the main page!

One way to counter that would be to store aggregated results for each territory (ward, municipality, province, district, whole country) in the database. This, however, would break normal forms pretty badly and I'm not sure if that's acceptable.

I'm thinking about database views. I'm imagining that the database would cache the results of views just as if I would break normal forms by adding relevant tables and columns myself and automatically recreate this cache for each larger area each time election results for the smaller area are modified (yes, editing the number of votes for each candidate in each ward is required). Can I make views work that way?

How else can I combat this issue?


texts: does merging tables always increases performance?

texts:
Consider we have data from visitors in a web site. And you are trying to create a database design for data mining.

There are some common fields which users have such as browser,ip, etc... . But there is a field such as email or something which for example 20% of people give.
My question is at what percent you should merge tables and where we should move the email or custom field to another table?

in this case, is database size the only drawback of merging tables? and is it's performance results better than seperating table?


texts:
What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN.  How to do it without mysqldump?

If using mysqldump, is the fastest way

$ mysqldump -h localhost -u foo -pbar mydb > mydb.sql

and then copy mydb.sql to desination machine and do

$ mysql -h localhost -u foo -pbar < mydb.sql

?   When importing, do I need to specify the database?


texts:
So we have 2 tables

users:

id | username | email
1  | testuser | test@test.com
2  | dirk     | test2@test2.com

data:

id | data         | username
1  | somedata     | dirk
3  | somemoredata | testuser

I want to select the username of the data table with the id 3 and then select his email. Which is the nicest way to do this with an inner join?


texts: Redundant plans in cache because of missing type length

texts:
Today I realized that our sql server's plan cach is stuffed with hundreads and thousands of nearly identical Compiled Plans.

The total number of plans is around 30.000 on a live system consuming around 4500 MB.
Lookin at them there are thousands that are nearly same.

Some samples:

(@ID uniqueidentifier,@GSS nvarchar(663))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(664))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(665))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(666))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(669))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID

(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(10))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(11))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(12))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(13))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID

The application uses sp_executesql almost everywhere and passes values as parameters, that is why I was sure the plans are getting reused.

But now it looks like the app does not care at all for string type's length and the length is added automatically depending on the in-fact length of the values causing a different plan for each combination of string length for each passed string value. So mostly it looks like the UPDATE and INSERT statements are the issue here. I guess some developers have saved some time at this point simply leaving the size away.

The use counts of these redundant UPDATE/INSERT kinds of plans are relatively low (ofthen 1, some up to 10, only few of then up to 40 usages).

Their size ranges between 0,05 MB and 2 MB. It is hard to tell exact values and amounts for possible savings because the statements can't be sorted properly because of the changing length constants in between. However I think an average plan size here is 0,17 MB in around 15000 Update/Insert plans where I guess a minimum of 80% could be saved, roughly that might be a potential saving of 2040 MB of the 2550 MB actually in use for those 15000 plans.
This would reduce the total plan cache usage from 4500 MB to sth. around 2500 MB.

Looks like we could easily save some memory here, right?

We are talking about sql servers with not a lot of memory (32 GB) that could really benefit from some additional free memory, however, I guess this is also a question of genreal system health.

So my question is, what can I do about it? Do I have ANY options on database side to get control about this? Are there any adhoc query optimization options I do not know yet to let's say ignore this?

Or is it really required that the developer correctly adds the type and typelength to the input parameters?

In case of (n)varchar types, the leading length is already defined in the table definition, so are there any downsides to expect from using exactly those?

Additional Info:

I meanwhile found K.Tripps post about plan cache getting wild... and her query shows me that I have 1500 MB & 20300 plans with use count of 1 in cache...

Is "optimize for ad hoc workloads" a silver bullet in my particular case?

Some more Info:
I fould out that the queries are created by SqlDataAdapter and SqlCommandBuilder. The Parameter Size for all the created parameters are not derived anyhow from the origin table by the commandbuilder. If they are left unset, the size of the actual values passed in us used as size value. This can be found here: "If not explicitly set, the size is inferred from the actual size of the specified parameter value."


texts: Vertica performance on select from view with union

texts:
We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others.

Now, we are doing a select from that view with some conditions(where).
On Oracle and SQL Server the performance of the select is reasonable.
We have given a try to Vertica, because it is supposed to be good with selects on large tables, but the performance of this particular select is really bad.

I understand that when you do a union you lose the indexes on columns, because you can't get back to them.

What do you think is the problem with Vertica, is it missing some optimization steps that the other databases do ?


texts: Autotask 'auto optimizer stats collection' and scheduler jobs logging

texts:
We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since
december Oracle has left more than 18 thousand of them with no statistics
renewed:
SQL> select to_char(last_analyzed,'YYYYMM') ,count(*) from dba_tables where
   2 owner='SYSADM' group by to_char(last_analyzed,'YYYYMM') order by 1;
TO_CHA  COUNT(*)
------ ----------
202012     18369
202101        23
202103        14
202104       832
           14490
Yet if I check one of them (as an example) within these 18369 tables, I find
it has been modified way above the 10% default threshold for stats
recomputation; select from dba_tab_modifications shows:
TABLE_NAME             INSERTS   UPDATES   DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
PS_JOB                    8063    181375       395 12-APR-21
SQL> select t.table_name,ts.stale_stats,
   2 t.monitoring,t.last_analyzed,t.num_rows from dba_tables t,dba_tab_statistics
   3 ts where t.owner=ts.owner and t.table_name='PS_JOB' and
   4 ts.table_name='PS_JOB';
TABLE_NAME          STALE_S MON LAST_ANAL  NUM_ROWS
-------------------- ------- --- --------- ----------
PS_JOB              YES    YES 08-DEC-20    162039
SQL> select count(*) from sysadm.ps_job;
 COUNT(*)
----------
   180794
In DBA_AUTOTASK_JOB_HISTORY or DBA_SCHEDULER_JOB_RUN_DETAILS views the
analyze-table jobs "ORA$AT_OS_OPT_SY_nnnnn" never take more than 35 to 40
minutes.
Yet this table is skipped from all stats recomputations that occur every day...
I have 2 questions:
o Someone knows if internal process doing stats recomputing job (i.e.
procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, which is not documented)
is logged somewhere with relevant details ?? (I'm very surprised that, given
the amount of tables this schema contains, it only takes a little more than
half an hour to complete...)
o What are conditions, or tests performed by DBMS_STATS in the background to
yield list of tables of which statistics should be recalculated ?? (why this
PS_JOB table hasn't been recomputed yet since Dec, 8th ??, in spite of its
growth)
In advance - thanks a lot.
Regards,
Sebino


texts:
I try to this in oracle XE 18c.
I cant't alter inmemory_size property but can alter sga_target.
Who relieve me?
SQL> alter system set inmemory_size=200M;
alter system set inmemory_size=200M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_target=1024M;

System altered.


texts: Can we replicate or restrict specific databases in MongoDB

texts:
In MySQL, we are having replicate-do-db/replicate-ignore-db during replication, where we can replicate or restrict specific databases from Master to Slave.

In the similar way, is there any specific configuration or work around in MongoDB replication to restrict specific databases and collections during data sync.

If not, is there any other way to sync only specific database or collections from Master to Slave.

I am using MongoDB 3.6


texts: Why is MDX Moving Average Calculation Inconsistent in Excel?

texts:
This question hurts my brain to talk about. Hopefully I can explain it properly.

I have the following calculation defined in my cube:

CREATE MEMBER CURRENTCUBE.[Measures].[52 Week Comp Net Sales Total]
 AS  SUM(
{[Dim Fiscal Weeks].[Week Id].CurrentMember.Lag(51) :
[Dim Fiscal Weeks].    [Week Id].CurrentMember}
,[Measures].[Comp Net Sales]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Sales / Trans / Units';

This ought to give me a 52-week trailing sum of sales, and it does... but ONLY when the cube is expanded to a weekly view in Excel. When it's rolled up to a Quarterly or YTD view, then it displays the sum of sales that occurred in the rolled-up-period only.

Here is a screen shot explaining what I mean:

What I don't understand is why the Quarter and Period (month) roll-ups don't include a full-year's worth of data. If a year's worth of sales trailing back from 1/1/2005 was 954,000, it doesn't make sense that Quarter 1, 2005 shows a year's worth of sales is only 252,000.

Can someone help me understand what I'm seeing? Have I done something wrong? How do I write a rolling average calculation that is accurate even when rolled up?


texts: Optimizing single-row DELETEs from a table with many FOREIGN KEY references

texts:
In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it.
If I delete one row from persons, it takes many minutes, which would not be a problem, but it also keeps the table locked, thus blocking the database for all processes. In the past, this has caused users to report that the system is down.
Of course the situation would greatly improve if I added supporting indexes for all the FKs (currently, only 20 of the 60 tables have it). But many of these FKs are for columns like modified_by, so all the indexes would have no other purpose, and would degrade performance of the system in the daily operations, only to gain an improvement in an exceptional case.
Before running the DELETE, I already make sure that all the referencing rows have been deleted or updated. I do it manually, since I am strongly against the use of CASCADE.
I am not considering soft-delete because otherwise I would have to change all software that reads the persons table to skip deleted rows.
Question
Is there a way to (possibly temporarily) change the locking mechanism of the persons table, so that even if the DELETE takes an hour, it does not impact concurrent processes?
Here's the anonymized execution plan.
Considerations

Disabling FKs might be a possibility. The risk is that someone else creates inconsistency while I delete the row, then I cannot re-enable the FKs.

Rows to delete: typically one at a time. Either manual operation or scheduled.

Interesting point: I did not check the execution plan immediately, but apparently, almost all operations cost 0%, except 7 cases of "Clustered index Scan (Clustered)" on the PK of referencing tables; one of them costs 57%, the other from 1% to 16%. I still don't understand why it should scan a clustered index.


texts: Can I send a string over TCP using T-SQL?

texts:
I've set up some alerts, based on this article

Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same machine of the SQL instance, and then the C# application sends the information to other machine).

My application can accept TCP connections so I was thinking about using that, but any other form for the SQL server to communicate with my application is also welcome.

Any suggestions in how I can do this?

*edit:*Being able to do an insert in another database(in other instance) via T-SQL would also solve my problem.

Background info:
I'm doing this to monitor when the Principal server is running exposed, and when the Mirror server is synchronized. So that if the witness goes down, and then the principal also goes down, based on the records of these alerts I can tell if its safe to promote the Mirror to Principal.

Note, that in this case scenario I cant read the mirror database without break the mirroring configuration, so using a table inside the Mirrored database is not a solution.


texts: Designing table for document annotation with future columns

texts:
I am working on a web application that allows students/researchers to annotate PDF documents shallowly by (manually) counting interesting phenomena. Right now, there is a total of about 45 of these phenomena, most of which are nominal values, booleans or integers. The database is a MySQL database, and the amount of documents would be between 100 and 500, so the database is quite small.

The application adds these documents and their respective annotations to a corpus which will also be searchable (and filterable based on a large subset of these phenomena).

In the future, some phenomena may be added, or existing ones may be disabled or automatised through automatic tagging, etc.

This could be done by, for example:

1. Altering the table and adding columns via the web application

Ultimately the table might become too big (column-wise), but it is easy to implement, even if it seems clumsy to have a table with 60 columns. I'm not sure if it is as inefficient as it seems.

2. Storing the annotation part of the web application externally

For example, as JSON documents or in a nonrelational database such as MongoDB. This intuitively makes sense as I'm presenting the need for a flexible (and thus nonrelational/schemaless) solution for annotation. But connecting two databases together might unnecessarily increase the complexity of the application. Also, using foreign keys might become impossible this way (?).

3. Storing the annotations as one blob in a MySQL column

I have virtually no experience with blobs, as I've always avoided them. I read it in a different question

4. Storing all/new attributes in an EAV table in the MySQL database

Seems like a hacky way to mimic nonrelational behaviour in a relational database. I also read that it could cause performance issues.

I'd love some advice on which method is better or if there is something entirely different I should have a look at.


texts:
Lets say I have two transactions, t1 and t2, that write to the same two rows, r1 and r2. I understand a kind of “dirty write” would be if t1 overwrites t2 on r1 last while t2 overwrites t1 on r2.

Is it possible for such a situation to arise? Does it depend on the isolation level used? What would prevent such a problem?


texts: Update the value when the row already exists

texts:
I have table 'A' in schema 'USER'. There are three columns in this table.
below are the names of the columns:

id (primary key)
name
age

I want to insert a row in this table, but if the row is already present (with same id) then the name and age should be updated instead of giving error sqlcode -803.

Is there any way to do this in a single query?


texts: Retrieving most recent X rows for each given user from a table

texts:
We've got a seemingly simple query that's running extremely slowly, but I can't quite figure out the best solution to the problem. The scenario is as follows.

We have an inbox table, which has the columns (with a few left out to keep things simple - the database structure is extremely old and not particularly well normalized):

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
recipient varchar(55) KEY
sender varchar(55) KEY
subject varchar(255) DEFAULT NULL
message mediumtext NOT NULL

This table has a number of rows, normally in the magnitude of 200,000+. The table uses the InnoDB engine.

One of our background tasks runs every night to try and manage the inbox to keep the table size down by archiving messages. It attempts to keep only the X most recent message for each recipient (where X is configurable - it is usually 30). Currently the system runs a query to get the IDs of the messages to keep, then deletes the messages where ID isn't in this list. The current query (which runs extremely slowly) to get the IDs to keep is:

SELECT  x.id
    FROM  inbox x
    JOIN  inbox y ON y.recipient = x.recipient
                 AND  y.id >= x.id
    GROUP BY (x.recipient), x.id
    HAVING  COUNT(*) <= 30;

This query runs extremely slowly and bogs the whole system down, with MySQL using very large amounts of CPU time. I enabled slow query logging, and got the below output:

# Time: 160208 10:51:17
# User@Host: root[root] @  [127.0.0.1]
# Thread_id: 29  Schema: test QC_hit: No
# Query_time: 3568.641446  Lock_time: 0.000718  Rows_sent: 1312  Rows_examined: 585499843
SET timestamp=1454928677;
Select  x.id
    FROM  inbox x
    JOIN  inbox y ON y.recipient = x.recipient
                 AND  y.id >= x.id
    GROUP BY (x.recipient), x.id
    HAVING  COUNT(*) <= 512;

The whole query seems like a bit of a mess, but I can't currently think of the best way to go about doing what I want. It's easy enough to get the single most recent message for each recipient, but I can't think of the best way of expanding that.

I've put together a quick SQL Fiddle with the database structure and a small subset of data, found here: http://sqlfiddle.com/#!9/04df7/1

I'm currently running MariaDB 10.0.23, which is the current most recent MariaDB 10.0 series release.


texts:
I want to do this

INSERT INTO AdminAccounts (Name)
SELECT Name
FROM Matrix

But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.


texts:
I had a few questions

If there is sufficient RAM, does Mongo load entire collection into memory?
Suppose I had two collections A & B and I know that there is not enough RAM to hold both - and if 90% of my queries are to collection A - is there a way to configure Mongo to prefer to keep A in RAM as much as possible?


texts: Want to use pivot and lag function together in SQL


texts: MySQL Error wth Relay logs - Could not open relay logs file for logging

texts:
We are receiving the following error.

2016-03-08 15:32:09 1184 [ERROR] MYSQL_BIN_LOG::move_crash_safe_index_file_to_index_file failed to move crash_safe_index_file to index file.

2016-03-08 15:32:09 1184 [ERROR] MYSQL_BIN_LOG::add_log_to_index failed to move crash safe index file to index file.

2016-03-08 15:32:09 1184 [ERROR] Could not use (null) for logging (error 9). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2016-03-08 15:32:09 1184 [ERROR] Could not open E:\Program Files\MySQL\MySQL Server 5.6\data\bin_relay_logs\relay\mysql-relay-log.001641 for logging (error 9). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2016-03-08 15:32:09 1184 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595

2016-03-08 15:32:09 1184 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595

2016-03-08 15:32:09 1184 [ERROR] Error reading relay log event: error switching to the next log

2016-03-08 15:32:09 1184 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-log.000821', position 4

2016-03-08 15:32:09 1184 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594

2016-03-08 15:32:09 1184 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-log.000820' position 104878880

I searched lots of posts but unable to get a solutions. Does anyone have a solution of it ?

Main thing is that, why it is happening ?

MySQL version: 5.6.24 in windows Platform

Windows server are virtual servers

This happened when system was running in Master - Master replication and Fabric enabled for DR.


texts: LOAD DATA statement returns a syntax error a fraction of the time

texts:
MySQL 5.5.32, MyISAM

A LOAD DATA INFILE statement usually executes and returns normally, succeeding at loading the data. On rare occasion it fails, returning a generic syntax error, always indicating the same character as the problem spot.

ERROR 1064 (42000) at line 36: 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 'ull, @field_name)'

I have no reason to believe there is a syntax error because the large majority of the time it succeeds, and it always succeeds on MySQL 5.5.19 and other versions. So all of these details might be red herrings. But, for what it's worth, that line in the statement is

field_name = if(@field_name='None', null, @field_name);

The question is whether this is a known bug, or a theoretically explainable behavior, and not what the syntax error is. Are there conditions in a database, or perhaps a file or filesystem, that could generate the same generic syntax error inconsistently but persistently like this?


texts:
Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using:

OPEN SYMMETRIC KEY !@#$%^ WITH DECRYPTION BY CERTIFICATE !@#%^&**^ 'string'


texts: Manually Grow DBs in an Availability Group

texts:
When you manually grow a data file on the primary replica in an Availability Group, which of the following happens?

The operation gets synced to the other secondary replicas.
I have to manually grow the files on all replicas of cluster.


texts:
My table definitions look like this:

I've created a view as:

users_questions(qid,username,question)

I want to create an instead of trigger on the view which puts the data
going to be inserted on the view to the table question.

I've tried:

CREATE OR REPLACE TRIGGER TRIGGER1
  INSTEAD OF INSERT ON USERS_QUESTIONS
  FOR EACH ROW AS
BEGIN
  INSERT INTO questions(qid,question)
    VALUES (:NEW.qid,:NEW.question);
END TRIGGER1;

It says something like:

  Error report - ORA-04079: invalid trigger specification
  04079. 00000 -  "invalid trigger specification"
  *Cause:    The create TRIGGER statement is invalid.
  *Action:   Check the statement for correct syntax.

Tried this also.

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF INSERT ON USERS_QUESTIONS
BEGIN
  INSERT INTO questions(qid,question)
      SELECT qid,question FROM inserted;
END;

Error:

  Error(4,3): PL/SQL: SQL Statement ignored Error(5,32): PL/SQL:
  ORA-00942: table or view does not exist

I am using:

Oracle OracleXE112
sqldeveloper-18.2.0.183.1748


texts: Is instant text search possible in big table with HTMLs?

texts:
I have a pretty big table with HTMLs (about 500K rows, average length of text is ~150K).
It is required to make fast search (less than 1s) rows which match let say '%<meta name="twitter:app%'.
First 10 results are enough.
Possible solution I have tired (on Postgres mainly):

Full-text search: results look not relevant and may miss something (can not find a way to improve it).
Trigram index (...USING GIN (t gin_trgm_ops)...) - a cool feature of PosgreSQL by the way. It worked fast on my synthetic tests, but when I applied to my HTMLs set - it worked minutes(!). I used explain anaylze, what showed me that index is used (what is nice), but than Postgres recheck what matched by index and this is quite slow, because it needs to make linear text search in large texts, one by one. On synthetic tests it worked because my texts were relatively small.
Also I tied Elastic (I used 'wildcard field type', Elastic use trigram index as well in that case), but performance was even worse that in Postgres case.

I still believe that it possible, but how?
Explain Analyse by Postgres:
[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Actual Startup Time": 209080.941,
      "Actual Total Time": 209080.942,
      "Actual Rows": 0,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Bitmap Heap Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Relation Name": "MyTable",
          "Alias": "MyTable",
          "Actual Startup Time": 209080.94,
          "Actual Total Time": 209080.94,
          "Actual Rows": 0,
          "Actual Loops": 1,
          "Recheck Cond": "(\"Content\" ~~* '%No agents%'::text)",
          "Rows Removed by Index Recheck": 15925,
          "Exact Heap Blocks": 2351,
          "Lossy Heap Blocks": 0,
          "Plans": [
            {
              "Node Type": "Bitmap Index Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Index Name": "TrigramIndex",
              "Actual Startup Time": 3.034,
              "Actual Total Time": 3.034,
              "Actual Rows": 15925,
              "Actual Loops": 1,
              "Index Cond": "(\"Content\" ~~* '%No agents%'::text)"
            }
          ]
        }
      ]
    },
    "Triggers": []
  }
]


texts: Keeping messages in a backup table after dequeue is completed

texts:
I would like to transfer the enqueued messages to a backup table after they have been dequeued.

Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE.

So after the message gets dequeued, the  queue table does not keep any entry.
But we need the message in a backup table once dequeued, so that we can check the backup table for debugging purposes.

If I use browse mode, would it help ? Are there any other alternatives?

I am using Oracle 11g.


texts: 'cmd' Field Definitions sysprocesses MS SQL Server

texts:
I am looking at doing some profiling of connections types and volumes across the day.

I am using the sys.sysprocesses to gather the information I require, looking specifically at the 'cmd' field to categorize connections. Does anyone have a good comprehensive list of definitions for the values in this field? I can figure out what a lot of them represent, but I can't find online any definitions for some of them, for example 'XE TIMER'

Many thanks,


texts:
I think I have all my bases covered but wanted to ask someone who knows better than I.  This is my syntax.
UPDATE SI
SET autoInsert = 0
FROM inserted
WHERE SI.autoInsert = SI.autoInsert
OR SI.autoInsert IS NULL;

and I want to set autoInsert = 0 where autoInsert = 1 or autoInsert IS NULL


texts: SQL Server stored procedure not throwing certain kind of error

texts:
I noticed an issue in my app recently where an database insert that occurred in a stored procedure was failing, but no exception was getting thrown. The insert failed because a column that doesn't allow nulls was getting assigned a null value. The error message was something like Error Code 515: Cannot insert the value NULL into column 'column_name', table 'my_table_name'; column does not allow nulls. INSERT fails.

Here is the stored procedure: https://pastebin.com/tLtjxgPL

So I have a TRY/CATCH with the error sql in the TRY portion. When it runs, it definitely gets to the CATCH portion, because I am creating a record of the error in my errors table. BUT... after that, I do a THROW which should pass the caught error back on up to PHP... but it doesn't. I've tried using a RAISERROR instead to no avail. I've also tried adding a SELECT in my CATCH block, but that never makes it to PHP.

Now, I can simplify that a bit, and get rid of all the transaction business, and I get this: https://pastebin.com/Faz5fUD3, which still has the issue.

But if I keep going, and get rid of the TRY/CATCH all together, it successfully passes the error up to PHP! Somehow, this specific type of error is causing MSSQL to not pass back any results or errors.

I have tested with various other types of errors (divide by zero, referencing a table that doesn't exist, explicit THROW, explicit RAISERROR) and they all behave as I would expect... catching the error and then re-throwing it so I can grab it in PHP.

Some details on my setup:

I am running this app on Windows 10, with XAMPP.
I am using the Slim framework, and using the Eloquent ORM. Since I'm using Eloquent... I believe I have to use the pdo_sqlsrv.dll rather than the sqlsrv.dll.
I have the live version of this same app on an Ubuntu box running Apache, using PDO and am NOT getting this issue.
I have a different app that is running on the same local machine, but with IIS. It is using the sqlsrv.dll, not PDO, and it doesn't have this issue.
I have tried setting the PDO attribute ATTR_ERRMODE to ERRMODE_SILENT, ERRMODE_WARNING, and ERRMODE_EXCEPTION, and no matter what, I cannot find a way to get PHP notified of this error.
I've tried taking that EXEC errors_add part out of my CATCH block, just to make sure that's not the issue, and it doesn't fix it.
On my local machine (where I'm experiencing this issue), I'm connecting to 'Microsoft SQL Server Developer (64-bit) 14.0.2002.14'
On my live version (where I'm NOT experiencing this issue), I'm connecting to 'Microsoft SQL Server Enterprise: Core-based Licensing (64-bit) 12.0.4213.0'

So... I feel like it has to be caused by the SQLSRV driver I'm using... or something about the settings or version of the DB I'm connecting to...?

Any help would be greatly appreciated. Thanks!


texts: Specify a different username for default Postgres user

texts:
I have the same issue as:
Specifying a username while running initdb causes error in log on start of PostgreSQL process

but I am working on setting up PostgreSQL on Ubuntu 17.04 instead of CentOS. I've read that calling initdb directly in this environment is discouraged because the suite of tools packaged with the software such as pg_createcluster, etc. What differences should I consider if I try initializing with a user other than the default postgres user?


texts:
I have big cpu caused by mysql,
3 vcores, centos 6 64bits

http://s3.postimg.org/z8l73e98z/mysql.png

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

#innodb_use_native_aio = 0
innodb_file_per_table

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

+----------------+-------------------+-------------------+-------------------+
| Storage Engine | Data Size         | Index Size        | Total Size        |
+----------------+-------------------+-------------------+-------------------+
| InnoDB         |           1.81 MB |         256.00 KB |           2.06 MB |
| Total          |           1.81 MB |         256.00 KB |           2.06 MB |
+----------------+-------------------+-------------------+-------------------+
2 rows in set (0.08 sec)

Running process right now not when was cpu 7

USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0  19364   436 ?        Ss   Mar12   0:00 init
root         2  0.0  0.0      0     0 ?        S    Mar12   0:00 [kthreadd/95734]
root         3  0.0  0.0      0     0 ?        S    Mar12   0:00 [khelper/95734]
root         4  0.0  0.0      0     0 ?        S    Mar12   0:00 [rpciod/95734/0]
root         5  0.0  0.0      0     0 ?        S    Mar12   0:00 [rpciod/95734/1]
root         6  0.0  0.0      0     0 ?        S    Mar12   0:00 [rpciod/95734/2]
root         7  0.0  0.0      0     0 ?        S    Mar12   0:00 [rpciod/95734/3]
root         8  0.0  0.0      0     0 ?        S    Mar12   0:00 [nfsiod/95734]
dbus       559  0.0  0.0  21540   316 ?        Ss   Mar12   0:00 dbus-daemon --system
root       639  0.0  0.0 117332   876 ?        Ss   Mar12   0:01 crond
root       682  0.0  0.0   4068    88 tty1     Ss+  Mar12   0:00 /sbin/mingetty     console
root       683  0.0  0.0   4068    88 tty2     Ss+  Mar12   0:00 /sbin/mingetty tty2
root       687  0.0  0.0   4068    84 tty3     Ss+  Mar12   0:00 /sbin/mingetty /dev/tty3
root       689  0.0  0.0   4068    84 tty4     Ss+  Mar12   0:00 /sbin/mingetty /dev/tty4

How to fix this ? If you need some more information just say.

Thanks.


texts: How to show the column names of a table?

texts:
The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but how do you see the individual column names?

(SELECT statement shows Empty set if no data is present and can NOT be thus used.)


texts:
I have a MySQL 5.5.23 DB that performs about 3k qps, 5% of which are writes. Lately, I've been having a big problem with random write freezes. Everything is going great, and then all of a sudden any write that comes in stops in the "update" or "Updating" state. They stay in this state for about a minute or two and then finally finish. The problem is that with such high amount of connections, when they freeze randomly like this, the connection stays open and then I inevitably get too many connection errors.

It's happening on all tables during both INSERT and UPDATE queries.

Has anyone seen this before? Is there anything that can be done about it?

I'm using Amazon RDS with the biggest instance they offer. I'd be more than happy to provide any information you guys need, just let me know what that is.

UPDATE: The main table I'm inserting into is:

CREATE TABLE `mytable` (
 `hash` varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `name` varchar(256) DEFAULT NULL,
 `ip` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `deletehash` char(15) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `datetime` datetime NOT NULL,
 `api_key` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
 `account_id` int(10) unsigned DEFAULT NULL,
 `type` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `width` int(10) unsigned NOT NULL DEFAULT '0',
 `height` int(10) unsigned NOT NULL DEFAULT '0',
 `size` int(10) unsigned NOT NULL DEFAULT '0',
 `animated` tinyint(1) NOT NULL DEFAULT '0',
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `lastviewed` datetime NOT NULL,
 PRIMARY KEY (`hash`),
 UNIQUE KEY `deletehash` (`deletehash`),
 KEY `datetime` (`datetime`),
 KEY `account_id` (`account_id`),
 KEY `ip` (`ip`),
 KEY `api_key` (`api_key`),
 KEY `views` (`views`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Sample insert:

insert into mytable (hash, name, ip, deletehashe, datetime, api_key, account_id, type, width, height, size, animated, views, lastviewed) values('abcde', 'file name', '127.0.0.1', 'abcdefghijklmn', '2012-05-31 00:00:00', NULL, NULL, 'mime/type', 0, 0, 0, 0, 0, '0000-00-00 00:00:00');

Sample update:

update mytable set views = views+1, lastviewed = NOW() where hash = 'abcde';


texts: Are file locations stored in PostreSQL catalog?

texts:
I am trying to learn a bit of PostgreSQL, and I am currently trying to understand the catalog. I think that if it stores all the metadata of the DB, should it also store things like the file locations?

I mean, I can (not involving the catalog) do:

SHOW data_directory;

or

SHOW config_file;

etc etc. I think it would be quite interesting to retrieve the same information via a catalog query, but I am not finding information about it. At most, I could do:

SELECT pg_settings.name, pg_settings.sourcefile FROM pg_settings WHERE pg_settings.sourcefile LIKE '%.conf%';

and kind of see where the files should be stored. I wondered if there is a more direct way to get that information.

Maybe is not possible?


texts:
Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not.

Bad code

SELECT COUNT(ip_address) FROM `ports` (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
)

Explained

The first query, when run on its own returns this:

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

ip_address
192.168.1.1
192.168.1.2
192.168.1.248
192.168.1.251
192.168.1.254

The second query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`

17

Question

I would like to know how to count that list of 5 IP addresses.

I have been looking online at possible solutions to this simple problem and just getting frustrated, so thought I'd ask the experts.


texts: Stored procedure execution time went from under 2mins to "infinity" without modifications

texts:
I have an SSRS report that pulls it's source from a vendor's MSSQL 2012 database. It's an extensive dataset so I've created a stored procedure that runs at the same time every morning.

There's quite a bit happening in that stored proc, but I had optimized it to run in about a minute and a half. It did so for the last 3 months without any issues until this morning. When I came in and checked the logs, I saw that the sp was still running (2 hours later). I waited a bit, then stopped it thinking it might just be hanging.

I tried executing it again and it's been running for over an hour again.

The only thing I can think of that changed was an update that the vendor applied to the database yesterday.

I tried to run a query to see if there were any locks on the tables that I'm pulling data from, but I don't have sufficient permissions. I also ran sp_who2 to get some info as I read on here that it might help out, but all I've learnt is the following:

Status = RUNNABLE | BlkBy = . | Command = SELECT INTO | CPUTime = 47 | DiskIO = 0

I'll be contacting the database administrator to troubleshoot, but I'd like to get an idea of what I can suggest, since the hourly rate for support services is very expensive and they often try to extend the call beyond what is necessary.

Is there something to look at right away when a stored procedure suddenly becomes inefficient overnight?