texts:
I have several types of writings: articles, projects, reviews, etc.

I have a table containing paragraphs (fields id (INT) and partext (TEXT)).

Each writing consists of several (ordered) paragraphs. So a description of a writing is a table consisting of fields id, seq (sequential number of the paragraph in the writing), par (the ID of the paragraph).

My question: Should I create a table for each kind of writings (individual tables for articles, projects, reviews, etc.) or create one table with ENUM field specifying whether it is an article, project, review, etc.?


texts: Sqlserver exec sp_execute to simple query?

texts:
I use sqlserver 2012 profile to watch all query that run on my db.

I saw only exec sp_execute 2,55,88 or exec sp_execute 1,43,675and command like that.

Why I cant watch update/select queries?


texts: What does a SQL Server DBA need to know about Windows Server?

texts:
Analogous to my previous question What does a DBA have to know about SSAS?, which generated a really wonderful answer (thank you, @ConcernedOfTunbridgeWells!), I now ask a similiar question:

What does a SQL Server DBA need to know about Windows Server in order to be able to manage it efficiently and understand what is going on behind the scenes and to which degree is it important to know the underlying OS in which SQL Server runs to in order to become a great DBA?

It can be individual features, books, from experience, you name it!


texts: MySQL: 2 separate queries run very fast, combined query with OR runs very slow

texts:
EXPLAIN
SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
    firewall.name as fw, proto.proto, service, action.action,
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
FROM log, action, firewall, proto, rule_uid
WHERE ( rule_uid.id = log.rule_uid )
    AND ( proto.id = log.proto )
    AND ( log.action = action.id AND action.action='accept' )
    AND ( log.orig = firewall.id)
    AND ( ( src >= inet_aton('0.0.0.0')
            AND src <= inet_aton('255.255.255.255') ) )
    AND ( ( xlatedst >= inet_aton('173.82.122.94')
            AND xlatedst <= inet_aton('173.82.122.94') ) )
GROUP BY src, dst
ORDER BY hits DESC, dst
LIMIT 100;

uses temporary/filesort, 92 lines, and runs in 30ms.

Similarly fast if I modify the query to search for the same IP in the dst column.

But if I combine both queries to search for that IP in the xlatedst OR the dst columns:

SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
    firewall.name as fw,  proto.proto, service, action.action,
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
FROM log, action, firewall, proto, rule_uid
WHERE( rule_uid.id = log.rule_uid )
    AND ( proto.id = log.proto )
    AND ( log.action = action.id AND action.action='accept' )
    AND ( log.orig = firewall.id)
    AND ( ( src >= inet_aton('0.0.0.0')
            AND src <= inet_aton('255.255.255.255') ) )
    AND *( ( xlatedst >= inet_aton('173.82.122.94')
             AND xlatedst <= inet_aton('173.82.122.94') )
         OR ( dst >= inet_aton('173.82.122.94')
              AND dst <= inet_aton('173.82.122.94') )
         )*
GROUP BY src, dst
ORDER BY hits DESC, dst
LIMIT 100;

then it examines 2121544 rows and runs for 3 minutes.

Now I could just run both queries and concat the results. But I wonder if there is a simpler way?

There is a seperate index on both "dst" and "xlatedst". Why isnt it used?


texts: Incorrect information in .frm file after a power outage?

texts:
CentOS release 6.3 (Final)
x86_64
mysql  Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1

SHOW SLAVE STATUS\G:

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1696
                   Last_Error: Error executing row event: 'Failed to read from the .par file'

/var/log/mysqld.log:

130706  2:26:04 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './reportingdb/v3_ban_date.frm'
130706  2:26:04 [ERROR] Slave SQL: Error executing row event: 'Failed to read from the .par file', Error_code: 1696
130706  2:26:04 [Warning] Slave: Failed to read from the .par file Error_code: 1696
130706  2:26:04 [Warning] Slave: Incorrect information in file: './reportingdb/v3_ban_date.frm' Error_code: 1033

v3_ban_date is a partitioned table. The .par file is... empty:

# ls -l v3_ban_date.par
-rw-rw---- 1 mysql mysql 0 Jul  6 01:54 v3_ban_date.par

and this is the .frm format (hexdump -v -C v3_ban_date.frm):

|partition.... PA|
|RTITION BY RANGE|
| (TO_DAYS(dt)).(|
|PARTITION p00 VA|
|LUES LESS THAN (|
|0) ENGINE = Inno|
|DB,. PARTITION p|
|1 VALUES LESS TH|
|AN (734653) ENGI|
|NE = InnoDB,. PA|
|RTITION p02 VALU|
|ES LESS THAN (73|
|4745) ENGINE = I|
|nnoDB,. PARTITIO|
|N p031 VALUES LE|
|SS THAN (734863)|
| ENGINE = InnoDB|
|,. PARTITION p04|
| VALUES LESS THA|
|N (734959) ENGIN|
|E = InnoDB,. PAR|
|TITION p05 VALUE|
|S LESS THAN (735|
|103) ENGINE = In|
|noDB,. PARTITION|
| p06 VALUES LESS|
| THAN (735160) E|
|NGINE = InnoDB,.|
| PARTITION p07 V|
|ALUES LESS THAN |
|(735210) ENGINE |
|= InnoDB,. PARTI|
|TION MERGER_2013|
|324 VALUES LESS |
|THAN (735316) EN|
|GINE = InnoDB,. |
|PARTITION pcurre|
|nt_2013324 VALUE|
|S LESS THAN (735|
|317) ENGINE = In|
|noDB,. PARTITION|
| MERGER_201375 V|
|ALUES LESS THAN |
|(735419) ENGINE |
|= InnoDB,. PARTI|
|TION pcurrent_20|
|1375 VALUES LESS|
| THAN (735420) E|
|NGINE = InnoDB).|

The partition definitions on the first slave looks like this:

00000000  1f 00 00 00 74 00 01 07  0c 00 00 00 0c 0c 0c 0c  |....t...........|
00000010  0c 0c 0c 0c 0c 0c 0c 0c  60 00 00 00 70 30 30 00  |........`...p00.|
00000020  70 31 00 70 30 32 00 70  30 33 31 00 70 30 34 00  |p1.p02.p031.p04.|
00000030  70 30 35 00 70 30 36 00  70 30 37 00 4d 45 52 47  |p05.p06.p07.MERG|
00000040  45 52 5f 32 30 31 33 33  32 34 00 70 63 75 72 72  |ER_2013324.pcurr|
00000050  65 6e 74 5f 32 30 31 33  33 32 34 00 4d 45 52 47  |ent_2013324.MERG|
00000060  45 52 5f 32 30 31 33 38  32 38 00 70 63 75 72 72  |ER_2013828.pcurr|
00000070  65 6e 74 5f 32 30 31 33  38 32 38 00              |ent_2013828.|
0000007c

I've tried to copy this file to the second slave, then used bvi to edit the date to 201375 (corresponding with what is in the .frm file), then tried to start the second slave, but it didn't work.

So the questions are:

How do I know what information is incorrect in the .frm file?
Can I re-create the .par file from the one on the first slave?

PS: I know I can rebuild the second slave but I think this is an interesting challenge.


texts: MySQL group replication - how to find queries causing replication lag?

texts:
How can we determine which queries are causing a set of MySQL servers running Group Replication to have replication lag?
We have a MySQL Group Replication cluster with 9 servers, and we are currently experiencing replication lag (i.e high values in performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE for several servers). We're using MySQL Community version 8.0.19.
We noticed that neither slow queries nor frequent queries are necessarily correlated with lag across servers. We have also noticed that some specific joins we had in our application were causing lag spikes and, once we removed them, the lag reduced. This is, however, not the case for all joins.
Is there a tool or a methodology we can use to find the queries causing the lag?


texts: Uninstalling / upgrading PostgreSQL on OSX

texts:
The problem
I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5.
Foresnsics
The running PostgreSQL processes are:
  503    89     1   0 Fri09PM ??         0:06.57 /Library/PostgreSQL/9.3/bin/postmaster -D/Library/PostgreSQL/9.3/data
  503   236    89   0 Fri09PM ??         0:00.02 postgres: logger process
  503   249    89   0 Fri09PM ??         0:01.10 postgres: checkpointer process
  503   251    89   0 Fri09PM ??         0:03.06 postgres: writer process
  503   252    89   0 Fri09PM ??         0:03.09 postgres: wal writer process
  503   253    89   0 Fri09PM ??         0:05.79 postgres: autovacuum launcher process
  503   254    89   0 Fri09PM ??         0:15.80 postgres: stats collector process

Which means that the binaries are located at:
/Library/PostgreSQL/9.3/bin/postmaster
/Library/PostgreSQL/9.3/bin/psql
...

and the data at
/Library/PostgreSQL/9.3/data

What have I tried
I made sure it was not installed using Homebrew.
Any idea which installation of PostgreSQL is installed on my mac, and how to upgrade it?


texts: Using the name of a table contained in a column of another table?

texts:
I have a table called MyTable with varchar columns named SourceID and ReferenceID, where SourceID contains a table name and ReferenceID contains a key value for the source table.
Is it possible in a query to refer to the source table's row assuming I know the name of the key column that matches the value in RefrenceID?
Sample:
SELECT
    *
FROM
    MyTable as MT
INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID

This doesn't work, obviously.  But is there a way to do this as opposed to dynamically building the SQL statement using the values of SourceID and ReferenceID and then executing the query?  More specifically, is there a way to make MyTable.SourceID in the example above be usable within the JOIN?
Further considerations
I found this article, and it has a trick like follows:
select
  E.EmployeeName, coalesce(s.store,o.office) as Location
from
  Employees E
left outer join
  Stores S on ...
left outer join
  Offices O on ...
where
  O.Office is not null OR S.Store is not null

The WHERE clause simply helps the outer joins behave like INNER JOINs.  This would work well enough if there were a limited number of table options and they all had UNIQUE constraints.  This gets me part of the way there but but I really want is to have the table names in the JOINs to be based on a column in Employees.
I haven't given up yet. Any and all help is greatly appreciated.


texts:
I have a PostgreSQL server with pgBouncer as connection pooler.
My application is running on Elixir.
This is my config file for pgBouncer:
* = host=X.X.X.X  port=5432
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin
pool_mode = transaction
ignore_startup_parameters = extra_float_digits
server_check_query = select 1
server_check_delay = 30
max_client_conn = 10000
default_pool_size = 5
min_pool_size = 3
reserve_pool_size = 3
server_reset_query = DEALLOCATE ALL;

When I use the transaction pooling I'm getting this error:
ERROR 08P01 (protocol_violation) bind message supplies 4 parameters, but prepared statement "ecto_323" requires 2

Then I changed it to session pooling mode. This time I'm getting this error:
ERROR 26000 (invalid_sql_statement_name) prepared statement "ecto_83" does not exist

How do I fix this from pgBouncer?
When I connect the DB directly, I didn't see any errors. It was running more than a year without any proxy. We are implementing pgBouncer now.


texts: duplicate key violates unique constraint when using multi-threading

texts:
I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where each report must have a unique unit_id, time, latitude and longitude), grabs a db connection from the connection pool, and then inserts the report into the reports table. The uniqueness constraint is created with postgresql and dubbed "reports_uniqueness_index". When I have high volume, I get a ton of the following error:

org.postgresql.util.PSQLException: ERROR: duplicate key value
  violates unique constraint "reports_uniqueness_index"

Here's what I believe the problem is. Before database insertion, I perform a check to determine whether a report already exists in the table with the same unit_id, time, latitude and longitude. If it doesn't then the report is valid and I perform the insertion. However, I think because I am using concurrency I have 50 threads each checking at the same moment if the report is valid and since none of them have been inserted yet, each thread thinks it has a valid report and when it goes to insert them at same moment, that is when postgresql raises the error.

I would like a solution that doesn't create any latency with the concurrency. I been trying to avoid using synchronized statement or a reentrant lock because the database insertions need to occur as quickly as possible. This is the insertion right here:

 private boolean save(){
        Connection conn=null;
        Statement stmt=null;
        int status=0;
        DbConnectionPool dbPool = DbConnectionPool.getInstance();
        String sql = = "INSERT INTO reports"
        sql += " (unit_id, time, time_secs, latitude, longitude, speed, created_at)";
        sql += " values (...)";
        try {
            conn = dbPool.getConnection();
            stmt = conn.createStatement();
            status = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if (stmt != null)
                {
                    stmt.close();
                }

                if (conn != null)
                {
                    conn.close();
                }
            } catch(SQLException e){}
        }

        if(status > 0){
            return true;
        }

        return false;
}

One solution I thought of was using the Class object itself as a lock object:

synchronized(Report.class) {
    status = stmt.executeUpdate(sql);
}

But this will delay insertsion for other threads. Is there a better solution?


texts: I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around 300MB. I have spotted it is very, very often VACUUMed and wonder if changing the fillfactor would help?


texts:
I have a database with two tables. Each table has different structure but they do share few common fields.
Simplified example:
Table1 field structure:
 Field1, Field2, Field3, Field4

Table2 field structure:
 Field1, Field4, Field3, Field5, Field6

Of course my database has better named fields and much more of them. As you can see both tables have Field1 (master key), Field3 and Field4.
Now I'm wondering if there is any possibility to somehow merge the data from these two tables into one, preferably without actually copying and duplicating the data (use of database relations or queries).
The third table should contain all common fields and some others (blank cells when there is no data available or perhaps some default data).
I know that in the long run it would be best to modify the database structure so that all common fields are stored in one table and all other fields in other relational tables but at this time I'm not allowed to make any changes to existing tables as my boss is afraid that this could lead to existing systems to crash.
There is a plan to update and finally merge those systems into one (that includes database restructuring) but I do need to do some statistical analysis before then so I'm looking for quickly implementable solution.
Performance isn't of critical importance for this as this would be only a temporary solution but is still desired because I don't want to wait for too long for retrieving the data nor do I want to cause any significant slowdowns to the whole database system.
So any suggestions are welcome.


texts: Auto update a column every 24 hours in Oracle database

texts:
I want to reset the values of a column every 24 hours. Is it possible? The column contains numeric values.

It's a ticket reservation system. I need to reset the number of tickets.


texts: How to protect Sqlite3 database with password that using in desktop application

texts:
I am creating database with Qtsql.QSqlDatabase using with in python. I want to protect my Database with password. I am using this database for my desktop applications. If it is possible, How to do this. or is there any or other databases way to create database with password protected.
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")#database
db.setDatabaseName("project.db")


texts: Selecting with varbinary(max) criteria (in the where clause)

texts:
Basic info

Database: SQL Server Express 2008 R2
Client: SQL Server Management Studio

Backround (skip if not interested):

A project I'm maintaining uses an ORM, which apparently stored my enum values (which inherit from Byte) into binary serialized .Net objects stored in a varbinary(max) field.
I only found out this was happening after a new requirement emerged dictating my code to run under medium trust. Since the .Net binary formatter needs full trust to be called, it started crashing on the enums.

To clean the mess up I need to create migration scripts that will convert these (varbinary(max)) values back to integer values. There are only a handful of different values so it shouldn't be a big problem (I thought).

The problem:

I am able to get string representations of the blobs when selecting:

SELECT BinValue FROM MyTable where Type = 'Object';

It returns a string '0x...(an array of hexadecimal values)'.

But when I try to select on the column using copy-and-paste making sure I have the exact binary equivalent:

SELECT ItemId FROM MyTable WHERE Value=convert(varbinary(max), '0x...')

it does not return any records.

So is it possible to solve this using a client (such as Management Studio)?

If so, what would be the correct syntax?


texts: ssrs report does not show range stock graph in preview

texts:
Hi I'm working on my ssrs range stock with line graph. The graph was worked properly. But now the graph display all data except the stock line.

I deployed the current graph, it working properly. Web shows the stock line. I restarted the report server. but it not working. Plz give me a suggestion.
Thanks in advance


texts: Same postgres query in two different instances with the same data but with different times

texts:
I have the same postgres query running in two different instances restored with the same dump file:

one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit')
one instance in compute engine vm in gcp => https://explain.depesz.com/s/LTUL ('PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit')

But the query in 1) (40s) is faster than 2) (400s) (execution time calculated in python)
What I tried:

ran without cache (restart the on prem gcp instance)
changed the where clause values
ran the query in different computers
analyze the basics of explain command (same plan and same indexes)

What are the main reasons for this?
My main hypothesis now is the network traffic. How can I test that?
Thanks in advance

both have the same postgres version and hardware configuration
I am not sure, but these times are the first run of each query and cache (maybe) is not involved
traceroute 1) = 13.864 ms / traceroute 2) = 32.469 ms


texts: How can I view what NLS settings are active for other session?

texts: I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2.  What can I query to show the NLS settings for a different active session - all of the NLS_* views reference my current session.


texts: Optimizing query for large table with `rand()` ordering

texts:
I have a MySQL database running for a multiplayer game server and I've been trying to identify a performance bottleneck. I got the slow query logs and the slowest queries by time are like this (the matchmaking system):

select `accountID` from GameAccountProfile where `active`=1 and
`lastActiveTime`<1482549810 and `inTutorial`=0 and `defenseRating`>0 and
`league`=4 and `protectedPeriod`>1482550190 and `lastActiveTime`>1481945090 and
`accountID`!=986702 and `accountID`!=10 and `accountID`!=10 and
`accountID`!=463126 and `accountID`!=184374 and `defenseRating`>=3.3530639 and
`defenseRating`<=6.2271186 and `level`>=-8 and `level`<=2
order by rand() limit 1;

There are currently about 1.3 million rows in this table. I read that the rand() sorting may be a serious performance hit and when I ran an explain on it in MySQL Workshop on the query, it did highlight it in red as being a performance hit.

Also, I realized that the only index for any of these where conditions is on the account ID column. I'm considering adding a composite index on the "active", "inTutorial", "level", and "league" columns as they don't change often (arranged starting with the least often changed).

How can I get a random account ID with these conditions without using rand() in an order clause? I need to ensure all accounts get targeted equally but rand() seems to be a clear performance hit.

Is there any other way I can improve the performance of this query?


texts: If PostgreSQL starts after copying data, is the data ok?

texts: I'm copying the data for a PostgreSQL database, that is, /var/lib/postgresql, from one server to another. The servers are identical except for the fact that one is 32bit and the other one is 64. I already copied the data once and PostgreSQL started, but, is there a way to verify the integrity of the data?


texts: Dimensional Modeling of a Loan Approval Process and Fact Grain(s)

texts:
Context:

I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:

Time(day)
Account
Opportunity
Channel

And I and getting to the modeling of fact table(s) around the loan approval and credit decisioning process. An opportunity will get one or more "Credit Reviews" which have a series of steps they go through before arriving at a "Credit Decision". My instinct is to model the fact grain at the credit review step level or maybe the credit review level (or have one fact for each grain).

I'm working with some analysts who are used to using a “canned report”, one flattened table extracted from the source system that is at the opportunity level, and finds a "best credit review", and do a lot of their reporting from that.

I'm trying to explain my credit approval fact and how it would work and they think it's "overly complicated" and seem to want a fact table that is one-to-one with the opportunity, which is a dimension. And contains information on only the "best" credit review. In my mind that seems odd. I haven't run across a fact table that is at the same granularity (one-to-one) with a dimension. Have you ever heard of this? It feels like they are just trying to reproduce what they are used to, a non-dimensionally modeled data mart table at the opportunity level.

I'm trying to explain the utility of having a fact table that is at a higher granularity than they are used to in order to support many questions about the data rather than one, but I seem to be running into a wall getting this concept across. I’m not sure who is right here.

My question is this: Is it every appropriate, say in this situation, to model a fact that has the same cardinality as a dimension (one-to-one) with a core dimension? What would you recommend for this situation?


texts: how to change a user password while checking the current password in oracle?

texts:
In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command:

alter user user_name identified by new_password;

My problem is that I don't know how to check whether the current password which the user has been entered in the form is correct or not ( as the passwords are encrypted in oracle)

How should I do this?


texts: Inserting rows into a truncated table will increase the size?

texts: Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the tables in it and then start inserting new rows across tables will it increase the size or it still the same 50 GB, what I am trying to do is reuse the same tables- I don't need the old data.


texts:
A specific table in my database keeps crashing. I suspect it to be a bad buffer setting in the my.conf file, because it shows this error before crashing (most of the time it needs to be repaired):

18:26:59 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './myDB/myTable.MYI'; try to repair it
18:54:57 [ERROR] /usr/libexec/mysqld: Out of memory (Needed 59542644 bytes) 141020
18:54:57 [ERROR] /usr/libexec/mysqld: Sort aborted

I don't think it is hard disk related, since tmpdir is set to /tmp/ and has around 48 GB of free disk space.

Input

mysql> SHOW VARIABLES LIKE 'tmpdir';

Output

tmpdir         /tmp/

Here is a look at the my.conf settings. Take special note of the parameter sort_buffer_size which has been set way to high. However, I'm unsure if it will produce this kind of error when set too high. This machine runs Ubuntu with 8 GB RAM.

[mysqld]
max_connections         = 1500
back_log                = 50
skip-locking
key_buffer              = 819M
max_allowed_packet      = 16M
table_cache             = 512
sort_buffer_size        = 2048M
read_buffer_size        = 16M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 64M
thread_cache_size       = 16
query_cache_size        = 16M
thread_concurrency      = 8
max_heap_table_size     = 512M
wait_timeout            = 60
interactive_timeout     = 60

Any thoughts on this issue?


texts: Partitioning a table vs moving entries to another table

texts:
I don't understand how partitioning helps me more or less than using a separate table to store older records.

I need to maintain a list of stock entries along with historical records about the stock.

So here I have the option that if some stock is discontinued, I can move that stock to another table that will be accessed only at the time of reports when a user wants to see old data.

Another option I have is partitioning the table, but as I don't know much about partitioning I am not sure if this will really help me.

Stock records will be accessed each time stock is removed from inventory.  Once the remaining quantity reaches 0, the item needs to remain in the table for reporting purposes only.  A new entry is created upon purchase of further stock.

I have gone through the dev.mysql documentation on Partitioning.

Questions

Can anyone shade some light on this ?
Is this the right way of doing this ?
Should we handle it differently ?


texts: ALTER SYSTEM DISCONNECT SESSION doesn't work

texts:
Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.

I login as:

sqlplus system/[password] as sysdba

I do this to list the sessions:

select sid,serial#,program from gv$session where type='USER' and machine!='this-server';

And the list looks like this:

       SID    SERIAL# PROGRAM

10   7883 toad.exe
11  42365 toad.exe
77  22493 toad.exe

Then I do:

alter system disconnect session '10,7883' immediate;

And is says:

System altered.

But nothing happens. The session is still there. What am I doing wrong?

P.S.: I'm pretty new to Oracle, I only used MySQL before.


texts:
When I try to connect the database using sql developer it gives the error below:

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

I have tried all the suggestions that I found on this website, but in vain, they are not working.

I checked the instance and database status, they are started and active. But why I still can not connect to that?

When I try the oracle - default database, that is ok, but I want to connect to my own database, So can you help me to solve that problem?


texts: Using the same table twice in a select returns duplicate rows

texts:
I'm currently trying to put together something that will tell me the name of two important tables each of our customers use. Sadly, the names of these tables are not always the same, so I've currently come up with the following T-SQL;

create table #test  (   [Database] sysname,
                                    [TenantId] sysname,
                                    [TasksTable] sysname,
                                    [TimeRecordTable] sysname   )

insert into #test
select      db_name() as [Database],
                SCHEMA_NAME(tbl.schema_id) as [TenantId],
                tbl.name AS [TasksTable],
                tbl2.name AS [TimeRecordTable]
from    sys.tables tbl,
            sys.tables tbl2
where tbl.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW')
and     tbl2.name IN ('TaskTimeRecord','TaskTimeRecord_NEW')
and     SCHEMA_NAME(tbl.schema_id) = '2'

select * from #test
drop table #test

When running this, I'm getting over 200 rows of duplicated data, instead of a single line that includes the results I'm looking for.

Does anyone have any suggestions on how to get this working?


texts: How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group?

texts:
I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-ver15

One of the steps creates an endpoint using a certificate:

CREATE ENDPOINT DIAG_EP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
      )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE InstanceX_Cert,
    ROLE = ALL
      )

The cluster is working and some months later I want to check on the expiration date of this certificate.  So I run this query:

select * from sys.certificates

This query returns the empty set.  This can't be right, I know the mirroring endpoint is using a certificate.  So then I run this query to list all the mirroring endpoints:

select * from sys.database_mirroring_endpoints

And indeed it says connection_auth_desc = CERTIFICATE and certificate_id = XXX in the response.  So it must be using a certificate.  None of the columns in the response actually say which certificate or what the expiration date is.

Does anyone have any guidance on how to track down this certificate used in the mirroring endpoint in the SQL Server Always On Availability Group and check its expiration date?  Also, how can I update this certificate in the future without causing downtime for the cluster?

Any help would be greatly appreciated!


texts:
I have a 30GB MySql DB, made of innoDB tables.
currently the tables character set is: "utf8_unicode_ci" and I want to change it to "utf8_general_ci", what the best way to do it? currently i'm going through each table and running ALTER TABLE some_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; and it takes for ever... is there a better way?


texts: Microsoft SQL Server 2008 R2 backup error

texts:
I'm trying to do a simple backup to an external disk and getting this error:

  Backup failed for Server 'Server-name'

Additional information:

  System.Data.SqlClient.SqlError: The media loaded on "C:\Program Files\Microsoft SQL.." is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
  (Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:\Program Files.. if the destination file is an external device?


texts: pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows)

texts:
Versions

current:

PostgreSQL: 9.3.5 64-bit
PostGIS: 2.2.3
notes: 2.2.3 is the latest available on StackBuilder for 9.3.5;

upgrade

PostgreSQL: 9.6 64-bit
PostGIS: 2.3.1
notes: 2.3.1 is the only version available on StackBuilder for 9.6, although in principle I could downgrade to an archived 2.3.0 from OSGEO's installer archives, but that's as close as I can get to matching versions (which it turns out would not resolve anything anyhow).

DB size (not counting tables under 1MB in size): ~230GB. Not trivial, but not 'big data' either. Large enough that pg_dump would be a monumental PITA.

The Problem

If I enable the PostGIS extension on the destination (9.6) and run pg_upgrade with the appropriate source and destination bin and data directories, I get

New Cluster database "postgres" is not empty
Failure, exiting

This is because the PostGIS extension creates a table (spatial_ref_sys) when installed; the table can't be dropped without dropping the extension.

If I disable PostGIS in the destination (9.6), pg_upgrade fails because the destination lacks the appropriate loadable libraries (hint: libraries to do with PostGIS).

pg_upgrade offers the dandy suggestion that I remove the functions using the libraries from the old installation, which sounds like a recipe for disaster to me.

UPDATE: just tried deleting one of the PostGIS functions (ST_Locate_Between_Measures() - I've never used it and am unlikely to). Can't be done without dropping PostGIS, which can't be done because all the geometry columns in the data have GIST indexes.

I had tried to make the PostGIS versions conformable across the two PostgreSQL versions, but that can't happen, apparently - at least, not on Windows.

My guess

My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

PostGIS folks tend to care a lot about how long stuff takes to do (try doing spatial comparisons on 3 million polygons without a GIST index to see what a long query looks like), so it stands to reason that someone will have worked out how to upgrade rapidly.

Reach Goal

In the best of all worlds I would pg_upgrade --link, because once I migrate to the new version, the old cluster is dead to me.

CODA/inb4

Why would anybody run a Windows machine to do anything that requires reliability?, I hear you ask.

Legacy systems ("It was like that when I got here, officer") plus inertia (both institutional and personal)

I'm going interstate this weekend, so maybe the DB will just have to pg_dump itself across while I'm gone.


texts: Generating a numeric pattern in query result set

texts:
I'm looking to add an artificial grouping to my result set. I can't utilize ROW_NUMBER or RANK.

Take the following result from SELECT Col1, Col2 FROM myTbl:

Col1     Col2
----     ----
x        y
x        z
y        x
z        x
x        z
y        x

I want to add a column that will count to 3 and then repeat so I can subdivide the result set if need be:

Divider  Col1     Col2
-------  ----     ----
1         x        y
2         x        z
3         y        x
1         z        x
2         x        z
3         y        x

Of course the easy way would be to loop through the result set with a cursor or temp table and add it on, but when the result set is 4 million rows, performance will be terrible.

Anyone know how to add this Divider column directly into a SELECT statement?

In reality, the query will retrieve the result set ordered by date but I still want the divider to count off in a fixed manner.  If I would try to partition and use row_number the sequence would be unpredictable.  Suppose I wanted to take the larger result set and spread it out over a set of queues but I want them evenly distributed by date to make sure each queue is processing the most recent data. If I would order by date and subdivide only one of the queues would get the most recent data.

I'm not actually returning the date in the result set; I am only ordering by it.


texts:
My situation looks like this:

Table STOCK_ARTICLES:

ID *[PK]*
OTHER_DB_ID
ITEM_NAME

Table LOCATION:

ID *[PK]*
LOCATION_NAME

Table WORK_PLACE:

ID *[PK]*
WORKPLACE_NAME

Table INVENTORY_ITEMS:

ID *[PK]*
ITEM_NAME
STOCK_ARTICLE *[FK]*
LOCATION *[FK]*
WORK_PLACE *[FK]*

The 3 FKs in INVENTORY_ITEMS reference the "ID" columns in the respective other tables, obviously.

The relevant tables here are STOCK_ARTICLE and INVENTORY_ITEMS.

Now there is a SQL job consisting of several steps (SQL scripts) that "synchronizes" the database mentioned above with another database (OTHER_DB). One of the steps inside this job is for "cleanup". It deletes all records from STOCK_ITEMS where there's no corresponding record in the other database with the same ID.
It looks like this:

DELETE FROM STOCK_ARTICLES
 WHERE
    NOT EXISTS
     (SELECT OTHER_DB_ID FROM
     [OTHER_DB].[dbo].[OtherTable] AS other
               WHERE other.ObjectID = STOCK_ARTICLES.OTHER_DB_ID)

But this step always fails with:

  The DELETE statement conflicted with the REFERENCE constraint
  "FK_INVENTORY_ITEMS_STOCK_ARTICLES". The conflict occurred in database
  "FIRST_DB", table "dbo.INVENTORY_ITEMS", column 'STOCK_ARTICLES'.
  [SQLSTATE 23000] (Error 547)  The statement has been terminated.
  [SQLSTATE 01000] (Error 3621).  The step failed.

So the problem is that it can't delete records from STOCK_ARTICLES when they are referenced by INVENTORY_ITEMS.
But this cleanup needs to work.
Which means that I probably have to extend the cleanup script so that it first identifies the records that should be deleted from STOCK_ITEMS, but can't because the corresponding ID is referenced from inside INVENTORY_ITEMS.
Then it should first delete those records inside INVENTORY_ITEMS, and after that delete the records inside STOCK_ARTICLES.
Am I right?
How would the SQL code look like then?

Thank you.


texts: RMAN log file with date and name in Oracle 11gR2 in Linux

texts:
I have a script and I want to create my log file with datetime and with unique name. It's my script:

.......
.......
rman target/  LOG='/u01/backup/logs/rman.log' <<EOF
.......
.......
.......
EOF

How can I do it? Thank you.


texts:
I needed to reinstall the WAMP server on Windows and in the process it reinstalled MYSQL as well. Now when I login to phpmyadmin, I can see my databases with the tables listed on the left side navigation panel. But when I click on the individual tables, it throws an error saying that the table doesn't exist. Browsing to this location C:\wamp\bin\mysql\mysql5.6.12\data, I see the folders with the database names. I also see files ending with *.ibd. The filenames are the same as the table names. With different databases, the folder contents are different. Some have files ending with *.ibd only. Some have a mix of *.ibd and *.frm. There is also a *.opt file in some folders. I do not know much about the admin side of MySQL. I only know enough to use PHP to interact with it for simple SQL statements. I have gone through some posts on how to possibly recovering the databases. But most of what was discussed there doesn't seem to either directly relate to my specific issue or are beyond my understanding.

Is there a simple enough way to restore the databases that is easy for anyone to follow? Or does it require previous experience with recovering databases?
Please suggest a few options.


texts: Split read/write requests over different read/write datasources

texts:
Recently I have run some performance tests on the application I work on, and it turns out that it didn't do really well ( the problem is mainly between the back and the DB). So as investigating the problem\solution, we have found out that using read/write datasources ( read/write master 1 or multiple reads slaves) could be a good way to go. As I found in those sources: http://fedulov.website/2015/10/14/dynamic-datasource-routing-with-spring/

To sum up the solution consists of defining the datasources, and before each transaction ( @transaction ) define which datasource should we use. But with already having a huge number of defined services and transactions ( my case) it seems too much time consuming to choose at every step which datasource to use.

Is there an automated way to split (select vs /post/update ) opreations ? or a project that serves as a proxy to route the queries. ( I have seen this question that was asked 9 years ago but I think certainly there are new solutions How to setup Hibernate to read/write to different datasources?).

Also I have read about latency problems between writing and reading, ( are we talking about ms, s latency ?) does the number of read instances influence the latency? what to do to prevent such behavior before staring to code. ( an architecture to adopt maybe ? a design pattern? )

Ps: I am using spring, spring data jpa, hibernate, postgresql, hikari connection pool. Thank your for time.


texts:
I would like to grant a Postgres user access to a public_data view and allow them to execute user defined SQL queries against it.

However, the view is quite large (5M rows), so I'd like to prevent users from executing queries that could accidentally return huge results sets.

CREATE VIEW public_data AS SELECT * FROM data limit 10; only seems to give me the first 10 results no matter what query I run against public_data.

Ideally, I'd like users to be able to run SELECT * FROM public_data WHERE ...; but somehow have LIMIT 10 automatically included prior to executing the statement.

Is there any way to do this without creating a custom function?


texts:
I have a master product table as like below :

CREATE TABLE dbo.[products](
    [id] INT NOT NULL IDENTITY(1, 1),
    [product_code] VARCHAR(100) NOT NULL UNIQUE,
    [price] FLOAT NOT NULL,
    [brand] VARCHAR(100) NOT NULL,
    [colour] VARCHAR(100) NOT NULL
);

So, if I create this table, a clustered index will be created on id column and a non-clustered index on product_code column.

And I am using this table in a website to show the products. And I will be using the sql query like below.

Query 1

SELECT * FROM dbo.[products]
WHERE [brand] IN ('brand1', 'brand2', 'brand3', 'brand4', 'brand5')
AND [colour] IN ('colour1', 'colour1', 'colour1')
ORDER BY [product_code];

And there is an another option to search for bulk of product_codes like below.

Query 2

SELECT * FROM dbo.[products]
WHERE [product_code] IN ('product_code1', 'product_code2', 'product_code3');

The conditions can be more in Query 1 and product codes can be more in Query 2.

Did I created the index properly?

Or is there any better way to improve the performance?


texts: SQL 2019 Database Engine Service Stopped

texts:
I'm having an issue with my new SQL 2019 server.  It seems that randomly the Database Engine service is stopping.  It has happened 4 times over the last 3 weeks.  There is no pattern as to what days/times it has been happening.  Here are some images from the log:

I really need help on this as my boss is on me to stop this from happening.  I'm open to any suggestions.  Thanks in advance for the help.


texts: How to do two actions in conditional UPDATE?

texts:
In a query  as

SET @x = 1

UPDATE table1 SET
col2 = IF(col1 = 'y', @x:=0, @x:=@x+1)

Every time reaching the value of y in col1, I want to SET the @x:=@x+1 and after that reset the x to 0.

In other words, I want to count the rows before the occurrence of rows ending to col1 = 'y'.

EXAMPLE

I want to UPDATE col2 as follows. In the above-mentioned query, rows for col2 is simply 0 for all col1 = 'y'.

id       col1       col2
1       d          1
2       s          2
3       w          3
4       y          4
5       s          1
6       c          2
7       y          3
8       s          1
9       y          2
10       s          1


texts: how to simulate a low-memory state when running a query

texts: I am trying to better understand how SQL Server's execution engine behaves in a low-memory state.  Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be equal to RequiredMemory.  (My guess is that there's an undocumented trace flag which will do that.  Does anyone know what it is?)


texts: MySQL Session sql_mode vs Global sql_mode

texts:
@@SESSION.sql_mode and @@GLOBAL.sql_mode is showing different options.

@@SESSION.sql_mode

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@GLOBAL.sql_mode

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

It seems like GLOBAL.sql_mode is taking precedence, is that right?

Why wouldn't a session setting overwrite a global setting?

Query ran:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

Result:


texts: History from SQL Agent Jobs (SQL Server 2005)

texts:
how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records
are these records stored in system databases, not only in plain log files
how can I get records generated by SQL Agent Jobs only
is this history settable,


texts: Trigger to insert data row into new table when old table is updated

texts:
I am trying to set up a trigger in SQL server that when the price of an existing item on table item is changed, it inserts that entire item (row) into table item_price_history, putting the old price as old_price and the updated price as new_price. I'm pretty stumped as to how to get this to work. I keep getting errors that item_id cannot be bound, or is an invalid column, but it is a column in both tables. Here is what I have so far:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS
BEGIN
  DECLARE @DATE_OF_CHANGE datetime;
  SET @DATE_OF_CHANGE = GETDATE();
  INSERT INTO item_price_history,item_id,old_price,new_price,date_of_change)
  VALUES (item_id,old.price,new.price,@DATE_OF_CHANGE)
END;


texts: What is the difference between views and materialized views

texts: What is the difference between PostgreSQL's materialized views and MySQL's views?


texts: What can be represented in a relational database that can't be represented in a graph database?

texts:
I read Blockeel's article that is comparing different paradigms how to represent data. He mentions following advantages of the relational paradigm in comparison to graph paradigm:

They can naturally represent non-binary relationships.
There is a standard way of associating information with each individual or relationship.
They naturally distinguish between different types of individuals, each type possibly having a different kind of information associated with it.

Can you agree on this list of limitations of the graph paradigm in comparison to relational paradigm?

And can you agree, that in the regard of expressiveness of the paradigms it holds: logic ⊃ relational ⊃ graph
?


texts: how do I work out why my stored procedure won't compile?

texts:
I have a stored procedure whose sql file is nearly 6000 lines in length.

It's part of a series of DB build scripts which are used in lots of environments to build a standard DB setup.

When I attempt to run it in one environment, it doesn't compile.

show errors;

gives me the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5552/10  PL/SQL: SQL Statement ignored
5552/22  PL/SQL: ORA-00942: table or view does not exist
5556/10  PL/SQL: SQL Statement ignored
5556/24  PL/SQL: ORA-00942: table or view does not exist
5566/12  PL/SQL: SQL Statement ignored
5566/26  PL/SQL: ORA-00942: table or view does not exist

In order to work out what's wrong, I need to figure out which table or view it can't see.  However, line 5552 of my sql file is empty, and line 5566 has no table name on it.

How can I figure out which tables it can't see?


texts:
I want to store a whole HTTP response in PostgreSQL database row.

I need the HTTP status, the headers and the body.

AFAIK there is no native data type for this.

How could I store a HTTP response?

Update

I have been thinking about this again. I guess it is best if I follow this pattern: I won't search anything inside the http response. It is like a blob. Everything I want to search in it while be extracted before and put into a different column. Up to now only the http status code will get used and it will get an own column.


texts: Unable to write to ndf after shrink emptyfile

texts:
I am doing some file balancing practice/experiments on AdventureWorks and pages stopped writing to the ndf file (AdventureWorks2014_Data2.ndf).

Early tests were working as expected.  Proportional fill, spreads new pages across multiple files in file group.

Several tests I was running should have written pages to *Data2.ndf but did not.

Getting very frustrated I tried to shrink the mdf to ndf and if failed.

  Msg 2556, Level 16, State 1, Line 3 There is insufficient space in the filegroup to complete the emptyfile operation.

Both mdf and ndf have 300MB allocated, this does not make sense. mdf has 187mB used, ndf has 0MB used,

There have been multiple t-log backups taken in the time span.

I created *Data3.ndf, and shrunk from mdf, everything (excpet meta data) moved to Data3.ndf, nothing goes to *Data2.ndf.

  Msg 2555, Level 16, State 1, Line 3 Cannot move all contents of file "AdventureWorks2014_Data" to other places to complete the emptyfile operation.

What is wrong with *Data2.mdf?


texts: Do I need to reindex mysql table after bulk delete?

texts:
I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as the storage engine. Do I need to change it? I think its better for concurrent insert and select. Please give your suggestions. Do I need to do reindexing?

Thanks in advance..


texts:
I am trying to join the following tables:

Table 1: users (id, name)
Table 2: pivot_table (user_id, user_id2)

This table hold the relationship between the users. One user can be related to many other users.

Table 3: contacts (id, user_id, name)

I am trying to select a user and then find its related users and contacts.
I have tried to use the join, i am successfully able to get either the contacts or the users but not the both.

I used the following query:

select
    contacts.id as contact_id,
    users.id as user_id,
    pivot.user_id2 as user2_id
from
    `contacts`
    inner join `users` on  `contacts`.`user_id` = `users`.`id`
    inner join `pivot` as `pivot` on `pivot`.`user_id` = `contacts`.`user_id`

Suppose i have the following data in the tables
Users:

id, name
1, Sahil
2, Shubham
3, Xyz

Pivot table data

id, user_id, user_id2
1, 1, 2

Contacts table data

id, user_id, name
1, 1, Abc
2, 1, XYZ

Now what is get from the query is

contact_id, user_id, user_id2
1, 1, 2
2, 1, 2

but what i need is

contact_id, user_id, user_id2
1, 1, null
2, 1, null
null, 1, 2

I am not able to figure out what wrong i am doing.

Thanks


texts:
We are writing tons of oracle stored procedures to handle our business logic. We would like to do a physical grouping for better maintenance and understanding.

I can see package is helpful for encapsulation and logical grouping... Is there anyway we can create namespace physically to group certain procedures based on business logic.

Please advice us on this...


texts: MySQL Correlated Subquery (with multiple columns) per row

texts:
MySQL documentation states that:

  Subqueries in the FROM clause cannot be correlated subqueries. They
  are materialized in whole (evaluated to produce a result set) during
  query execution, so they cannot be evaluated per row of the outer
  query.

Okay, fine, I understand why this is. However, what if you want to repeat a specific query on multiple values?

SELECT COUNT( returning_visitors.per_ip ) AS count, AVG( returning_visitors.per_ip ) AS num_visits
FROM (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = ___INPUT___
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip >1
) AS returning_visitors

Let's say I want to retrieve the result of this query on multiple values in the form of a result table, using the values from SELECT id FROM sites. Is there a way to do this in a single query without using a stored procedure? Something like:

SELECT id, COUNT( returning_visitors.per_ip ) as readers, AVG( returning_visitors.per_ip ) as avg_visits_pr
FROM sites
SUBQUERY-PER-ROW (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = sites.id
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip > 1
) AS returning_visitors
WHERE sites.id IN (162888, 42705, 11412)

SUBQUERY-PER-ROW here is pseudo-code. JOIN will not work because of the above quoted restriction.

EDIT:

Another attempt at pseudo-code using info from http://dev.mysql.com/doc/refman/5.7/en/row-subqueries.html:

SELECT id, (
    SELECT COUNT( per_ip ) AS count, AVG( per_ip ) AS num_visits
    FROM (
        SELECT COUNT( * ) AS per_ip
        FROM site_hits_unique
        WHERE site_hits_unique.site_id = sites.id
        AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
        GROUP BY site_hits_unique.site_id, site_hits_unique.ip
        HAVING per_ip >1
    ) AS returning_visitors
) as (readers, avg_visits_pr)
FROM sites
WHERE sites.id IN (162888, 42705, 11412)

(it doesn't work, this isn't actual valid MySQL syntax, just a pseudo-code example)


texts: Manually VACUUM bloated tables once `hot_standby_feedback` is on on postgresql?

texts:
As seen in this answer by setting postgresql's hot_standby_feedback on it may cause having bloated table on long running queries. Therefore, in case that size goes out of control (my database consumes way too much space because of dead rows) to be able to handle it.

Therefore, I thought to enforce a VACCUMM action in this situation to delete stale and unused rows in case that storage goes out of controll as immediate action.

But how I can VACUUM all the tables of my database manually? Also, how I can make a dedicated user that is able only to VACUUM in order to create custom scripts that I can run in these situations?


texts:
I have a table as below, order field is like auto increment, but it is not defined as auto_increment because i do not want any gaps in that value

    +--------------+
    |  id  | order1|
    +--------------+
    |  115 |     1 |
    |  116 |     2 |
    |  117 |     3 |
    |  118 |     1 |
    |  119 |     2 |
    |  120 |     6 |
    |  121 |     7 |
    |  122 |     8 |
    +--------------+

Like if i delete row with id 118  rows with id 119 should have order1 as 3 and rowId 120 should have 4  rowId 121 5

basically i want the order1 column be serial no with no gaps when there are any deletion


texts: Deciding EC2 Instance type & Volume IOPS for Mongo DB

texts:
We have 500GB data in our Mongo(v2.6.3) including the indexes.

We currently run this on AWS m3.large(8GB RAM) instance. Yes, Single
machine.

Our MongoDB Cloud Manager shows these stats:

  Opcounters - 1.5k
  Background Flush Average - 5s
  Non mapped Virtual Memory - 2GB
  Lock Percentage - 15%
  BTree - 6k
  Connections - 2k
  Queues - 100
  Record Stats - 200
  Page Faults - 1k

Our Mongo has become slower over the last few months as the Data is growing rapidly at the rate of 40GB per month.

We are sure this is the time to scale it up. We had difficult time deciding whether to scale it horizontally or vertically. What we decided then was that let's scale vertically now. And six months down the line, as we will have more bandwidth in terms of people(currently we are a very small team), we will scale it Horizontally. We would love to have your views on this decision.

And moreover, the biggest confusion that we have is over choosing hardware.The options we have are:

AWS Ec2 r3.large - 2 CPU Cores - 15GB RAM
AWS Ec2 r3.xlarge - 4 CPU Cores - 30GB RAM

And we will be taking 1.5TB Volume but still confused with the number of IOPS that we need to reserve.

Hoping to get your views & valuable suggestions.


texts:
I have a windows application that accesses a SQLServer database.  Sometime within the last week or so it stopped working on other peoples machines with an exception thrown indicating they did not have execute permissions on a stored procedure.  I am wondering what mechanism might have changed my database (certainly I did not do anything . . . at least knowingly).

I have setup a role called db_execproc, added users (the application connects using windows authentication), and granted the role EXECUTE on the dbo schema.  Everything was working fine, but for some reason it stopped working.  Granting EXECUTE again to that role fixed the problem.

So I am wondering how this might have been broken (SQLServer update?).

Also, in the SQL Server Management Studio I was trying to verify that db_execproc had the right permissions, but I cant see anything (either before I granted them or after) . . . how can I see what permissions have been granted to a role?


texts:
I am installing an Oracle 12.1.0.1 Enterprise Edition on RHEL 6.4
I set it up with ASMM and HugePages  (64G of RAM)

There may be a correlation between the errors and signing in to em express (not 100% certain yet).  I mention this because of the following blog post with a similar error: http://surachartopun.com/2013/06/learn-something-wtih-12c-and-enjoy-bug.html

I have opened an SR already but wanted to see if anyone had thoughts on this.

Fri Aug 30 14:53:22 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x5] [PC:0xB71F161, qervwRowProcedure()+113] [flags: 0x0, count: 1]
Errors in file /home/oracle/diag/rdbms/prdtdr1m/prdtdr1m/trace/prdtdr1m_s000_5987.trc  (incident=33811):
ORA-07445: exception encountered: core dump [qervwRowProcedure()+113] [SIGSEGV] [ADDR:0x5] [PC:0xB71F161] [Address not mapped to object] []
Incident details in: /home/oracle/diag/rdbms/prdtdr1m/prdtdr1m/incident/incdir_33811/prdtdr1m_s000_5987_i33811.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Aug 30 14:53:24 2013
Dumping diagnostic data in directory=[cdmp_20130830145324], requested by (instance=1, osid=5987 (S000)), summary=[incident=33811].
Fri Aug 30 14:53:33 2013
Sweep [inc][33811]: completed
Sweep [inc2][33811]: completed


texts: Consults and counts in multiple databases

texts:
I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer.
My problem - I have 3 tables:

Clients

------------------------------------------------------
| id |        name        |          email           |
------------------------------------------------------
| 1  | Client 1           | client1@somecompany.com  |
| 2  | Client 2           | client2@somecompany.com  |
| 3  | Client 3           | client3@somecompany.com  |
| 4  | Client 4           | client4@somecompany.com  |
| 5  | Client 5           | client5@somecompany.com  |
| 6  | Client 6           | client6@somecompany.com  |
| 7  | Client 7           | client7@somecompany.com  |
| 8  | Client 8           | client8@somecompany.com  |
| 9  | Client 9           | client9@somecompany.com  |
| 10 | Client 10          | client10@somecompany.com |
------------------------------------------------------

Products
--------------------------------
| id | client_id |  item_name  |
--------------------------------
| 1  | 6         |  something  |
| 2  | 4         |  something  |
| 3  | 6         |  something  |
| 4  | 2         |  something  |
| 5  | 5         |  something  |
| 6  | 7         |  something  |
| 7  | 9         |  something  |
| 8  | 10        |  something  |
| 9  | 1         |  something  |
| 10 | 5         |  something  |
| 11 | 8         |  something  |
--------------------------------

product_status

------------------------------
| id | product_id |  status  |
------------------------------
| 1  | 1          | new      |
| 2  | 2          | used     |
| 3  | 3          | new      |
| 4  | 4          | used     |
| 5  | 5          | used     |
| 6  | 6          | new      |
| 7  | 7          | used     |
| 8  | 8          | used     |
| 9  | 9          | new      |
| 10 | 10         | used     |
| 11 | 11         | new      |
------------------------------

I need know the following:

Id and name of client
How many items has the client
How many items "new" and "used"

Like this

expected results
-----------------------------------------------------------------
| client_id | client_name | total_items | Items_new | Item_used |
-----------------------------------------------------------------
| 1         | Client 1    | 1           | 1         | 0         |
| 2         | Client 2    | 1           | 0         | 1         |
| 3         | Client 3    | 1           | 0         | 1         |
| 4         | Client 4    | 1           | 0         | 1         |
| 5         | Client 5    | 1           | 0         | 1         |
| 6         | Client 6    | 2           | 2         | 0         |
| 7         | Client 7    | 1           | 0         | 1         |
| 8         | Client 8    | 1           | 1         | 0         |
| 9         | Client 9    | 1           | 0         | 1         |
| 10        | Client 10   | 1           | 0         | 1         |
----------------------------------------------------------------

How can I achieve this?
Thanks for your help!


texts: Restoring a database using Postgres data files instead of .sql files

texts:
I backed up my Ubuntu 14.04 system using rsync to a USB stick, but apparently it wasn't formatted for booting. The original disk has been erased and I put a fresh install of Ubuntu on it. In migrating my data I forgot to dump one of my postgres databases to SQL, and I need to migrate it to my new install of Ubuntu.

I'm considering formatting a bootable disk and then attempting to rsync my old system to that for disk so I can boot and dump my database to SQL. But that's a discussion for a different forum.

I'm wondering if it's possible to use the data files from my old system to restore my database (i.e. copy /var/lib/postgresql/9.3/main) from my old install to my new install? Has anyone ever done this? I did try just copying the data file but it caused Postgres to break beyond repair. I had to delete it entirely and reinstall.

I know it's possible with MySQL, but I'm less familiar with Postgres.

Thanks!


texts: DR site SQL Server DB server not need Quorum Disk?

texts:
We have two SQL Server DB Servers and Shared disk in production site and 1 DB in DR Site.

Production site has Quorum Disk on Shared Storage.
Production site DB servers can access this share storage, but DR site SQL DB Server can't access it.

If Production site SQL DB Servers are down, will Windows Server Failover Cluster be Online or Offline?

Does DR Site SQL Server need Quorum disk?


texts:
What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this?

I have a situation where I am trying to migrate from db2 to mysql. In DB2 a field is referred to as timestamp but MySQL's timestamp does not support values greater than 2038-01-19 03:14:07, so I have used datetime. Before implementing datetime I want to do an impact analysis.


texts: Oracle error: UDI-00018: Data Pump client is incompatible with database version 11.01.00.06.00

texts:
I'm trying to use data pump to move the database to a different server with this command:

impdp dbuser/dbpsw@dbserver directory=data_pump_dir network_link=sourceDB schemas=EMP

But I got this error:

  UDI-00018: Data Pump client is incompatible with database version
  11.01.00.06.00

The source DB version is:

  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
  Production      PL/SQL Release 11.1.0.6.0 - Production
  CORE  11.1.0.6.0  Production
  TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
  NLSRTL Version 11.1.0.6.0 - Production

The target DB version is:

  Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
  PL/SQL Release 11.2.0.2.0 - Production
  CORE  11.2.0.2.0  Production
  TNS for Linux: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production

Does it mean I need to upgrade source DB version to be same as target DB?
If I can't upgrade database on the source DB, can I only upgrade the impdp utility? If yes, how?

Thank you for your time.


texts:
I am currently in an live environment and I am trying to clear some disk space. I noticed that a SQL Server database transaction log file is kinda big (12GB).

The recovery model of that database is set to simple. How can I decrease its size without killing any data or wrecking havoc on the server?


texts: MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT?

texts:
A tutorial is asking me to encrypt passwords using SHA512 in the following manner:

INSERT INTO `vir_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('pw1', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
('2', '1', ENCRYPT('pw2', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');

How would I replace this with a MySQL 5.7 non-deprecated AES_ENCRYPT instead of the old ENCRYPT way of doing things?

MySQL's documentation is severely lacking in this case at it does NOT provide any clear examples.

I found a way of doing it outside the DBMS, however I would like to keep it all contained and not dependant on external code and/or tools; that's why would like to know.

When I run this query now, I get this error message:

'ENCRYPT' is deprecated and will be removed in a future release. Please use AES_ENCRYPT instead

update:
The reason it's using this is because I need to store the password in such a way that Dovecot's default_pass_scheme (which is set to SHA512-CRYPT) can validate my passwords.

The $6$ part of the passwords seems to be important.. I don't know why, but all passwords that are generated the old way start with that. Please check the tutorial on why this is.


texts: Passing Variable in json_value in sql server 2016

texts:
declare @id int
set @id = 1754
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;

Its working with sql server 2017 but error showing in sql server 2016.

  Error : Msg 13610, Level 16, State 1, Line 3 The argument 2 of the
  "JSON_VALUE or JSON_QUERY" must be a string literal. Problem in
  converting INT to vachar "'+cast(@id as varchar)+'"


texts:
I have a backup device set for my transaction log so the sets are contained in 1 file.
I have this set to append, is this correct or should it be overwrite in the plan?
After the full backup is taken and then the next log backup, I still see additional sets in the file.

Update

Ok I tested this an over writing loses all other sets. So is there a way to purge this after a full backup has been taken from a plan?


texts: How does MySQL handle indexes and where clauses with joins

texts:
I am querying large tables with joins and where conditions.
An example query:
SELECT *
FROM forename
JOIN forenameLength ON forenameLength.nameRef = forename.nameRef
WHERE forename.placeRef = 18000 AND forenameLength.length = 3

Indexes:

forename: nameRef, placeRef
forenameLength: nameRef, length

forename + forenameLength are indexed on nameRef and this index is used to quickly join them. If I extend forenameLength index to cover nameRef, length MySQL does not use the index for the condition forenameLength.length = 3.
Is there any way allow the index to be used for the where condition?
If I change the query to:
SELECT *
FROM forename
JOIN forenameLength ON forenameLength.nameRef = 1 # change
WHERE forename.placeRef = 18000 AND forenameLength.length = 3

i.e. the join is fixed, it then uses the index for the where condition forenameLength.length = 3.
Tabe outputs:
CREATE TABLE `forenameLength` (
  `nameRef` int(8) UNSIGNED NOT NULL,
  `length` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `forenameLength`
  ADD PRIMARY KEY (`nameRef`,`length`) USING BTREE;

  CREATE TABLE `forename` (
  `random` int(8) UNSIGNED NOT NULL,
  `placeRef` int(8) NOT NULL,
  `nameRef` int(8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `forename`
  ADD KEY `placeRef` (`placeRef`,`nameRef`) USING BTREE;

Explain for 1st query:

the occurrence of using where is what I am trying to solve
Using where is caused by forenameLength.nameRef = forename.nameRef. As mentioned changing it to forenameLength.nameRef = 1 removed using where from EXPLAIN. This second query (from above) provides the EXPLAIN:

UPDATE: Is it possible someone could comment - is it normal behaviour that an index on more than one column (as above) uses an index on a join, but not on where? I have been working on this for days, with no luck, no leads.


texts: Creating User for a single contained database

texts:
I am pretty new working with MSSQL and have 2012 version at the moment. What I'm trying to do is create a user for a contained database, and give that user access only to that database.

I will invoke this SQL from JAVA using a prepared statement.


texts: Find parent rows that have identical sets of child rows

texts:
Suppose I have a structure like this:
Recipes table
RecipeID
Name
Description

RecipeIngredients table
RecipeID
IngredientID
Quantity
UOM

The key on RecipeIngredients is (RecipeID, IngredientID).
What are some good ways for finding duplicate recipes? A duplicate recipe is defined as having the exact same set of ingredients and quantities for each ingredient.
I've thought of using FOR XML PATH to combine the ingredients into a single column. I haven't fully explored this but it should work if I make sure the ingredients/UOMs/quantities are sorted in the same sequence and have a proper separator. Are there better approaches?
There are 48K recipes and 200K ingredient rows.


texts: Pass data from a table row into a create login statement using dynamicSQL

texts:
I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.

We've done NO work concerning cursors/dynamicSQL because our instructor said we'll never need it. I don't think I'd have any problems with an action query within a cursor, but I'm stuck on how to pass data from a table row into a CREATE LOGIN statement, within a cursor, using a dynamicSQL string.

Should the cursor be nested inside the DynamicSQL code? Vice versa? I haven't attempted to run it as it's not finished, but I think I'm on the wrong track anyway. Can anyone help? Thanks.

Here are the lab instructions and my (incomplete) code so far.

LAB Instructions:

Write a script that uses dynamic SQL and a cursor to loop
through each row of the Administrators table and

(1) create a login ID for each row that consists of the administrator’s first
and last name with no space between;

(2) set a temporary password of “temp” for each login;

(3) set the default database for the login to the MyGuitarShop database;

(4) create a user for the login with the same name as the login; and

(4) assign the user to the OrderEntry role you created in Exercise 1

My Code:

-- Create Dynamic SQL
DECLARE @DynamicSQL varchar (max)

-- Cursor variables
-- Should "SET @DynamicSQL =" go before the cursor variables?
DECLARE @FirstName varchar(255), @LastName varchar(255),
        @updateCount int;  -- needed?

-- Create cursor, select table rows through which to loop
DECLARE Admin_Cursor CURSOR FOR
    SELECT FirstName, LastName  -- Data needed from rows?
    FROM Administrators         -- Table with needed rows
OPEN Admin_Cursor;              -- Open the cursor

-- Get values from first row and store in cursor variables (FirstName/LastName)
FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;

-- set loop condition/begin loop
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @DynamicSQL = (CREATE LOGIN 'FirstName' + 'LastName' -- this is where Im lost
    -- some code
    -- some code

     -- begin next iteration of loop
    FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;
    END;

CLOSE Admin_Cursor;         -- Close the cursor
DEALLOCATE Admin_Cursor;    -- Erase from database?
EXEC (@DynamicSQL);         -- Execute the dynamicSQL string

Thanks to anyone willing and able to help.


texts: Query for sub-array in multi-dimentional array

texts:
How do I fix the following sql query:

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', '{{1,2,3},{4,5,6}}'),
  ('rowb', '{{4,5,6},{7,8,9},{10,11,12}}');

This following query triggers a syntax error. How do I query for values with this sub array?

select * from array_test where '{1,2,3}' = ANY (array_column);


texts: Upgrading using pg_upgrade on windows fails

texts:
When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails:

RUNAS /USER:postgres "CMD.EXE"

After some investigation, it seems there is no postgres windows user on the machine.  This causes the pg_upgrade command to fail as well.

How should I run the upgrade process?


texts:
I am trying to find out the queries which would have run and caused bloat to TempDB.

I am able to pull data like TempDB bloating on one particular day where it almost ate upto 300 GB of space, was somewhere in middle of last week.

As per post How to find the SQL statements that caused tempdb growth?, i can find below data but not sure how to interpret this as i can find host and login, but not the actual queries.

status  CPU TIME (in milisec)   Total Scheduled TIME (in milisec)   Elapsed TIME (in milisec)   Memory USAGE (in KB)    SPACE Allocated FOR USER Objects (in KB)    SPACE Deallocated FOR USER Objects (in KB)  SPACE Allocated FOR Internal Objects (in KB)    SPACE Deallocated FOR Internal Objects (in KB)  SESSION Type    ROW COUNT
sleeping    4428523 4618020 5418422 24  0   648 38349248    38351104    user session    1
sleeping    4411334 4578428 5389351 24  0   672 41238720    41240448    user session    1
sleeping    4601627 4820643 5631623 24  0   720 40119104    40121280    user session    1
sleeping    12887505    13667397    15209924    24  0   3528    46007808    46013184    user session    1
sleeping    4589595 4792873 5835456 24  0   528 39140800    39142528    user session    1
sleeping    6416738 6767615 7836356 24  0   1088    40406400    40408768    user session    1
sleeping    8626603 9072173 10416679    24  0   1488    43888640    43891584    user session    1
sleeping    4346345 4554241 5389904 24  0   976 39358208    39360128    user session    1
sleeping    11817344    12427608    13494857    24  0   3208    46888512    46894208    user session    1
sleeping    5182063 5435636 6242939 24  0   680 37975040    37977024    user session    1

Is there a way i can really find queries which caused almost 300 GB bloat last week.

Yes,  am aware of 3rd party tools which would have save the info. But we do not have one on this. How can i find one if possible?


texts: Is there a way to enable MariaDB MEMORY storage engine for every table?

texts:
I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on mariadb.com and it seems like I had to specify storage engine at the creation time.

What I want is to run a single command that enable MEMORY storage engine to every table on an instance. Is there anyway I can achieve that?


texts: Can database administrators change physical schema?

texts:
I was reading the Chapter 1 of the 7th edition of Database System Concepts and ran into this:

• Schema and physical-organization modification. The DBA carries
out changes to the schema and physical organization to reflect the
changing needs of the organization, or to alter the physical
organization to improve performance.

Is it true that database administrators can access the physical schema and change it or have I misunderstood what is cited above?


texts: Unique, Non-Clustered Index and Unique Clustered Index on Same Column

texts:
I've inherited a project and I'm seeing something pretty interesting that I haven't come across in the past.  Looking for guidance.

The Primary Key on a table is a Unique, Non-Clustered Index and there is also a Unique Clustered Index on same column.

Is there any situation where you'd want this, or should I make alterations so that the primary key is a unique, clustered index?

Thanks in advance.


texts: Optimization problem: compound clustered keys, flag conditions and index-merge

texts:
Three tables:

product: with columns: ( a, g, ...a_lot_more... )

a: PK, clustered
g: bit-column

main: with columns: ( c, f, a, b, ...a_lot_more... )

c: PK, clustered
f: bit-column
(a, b): UQ

lookup with columns: ( a, b, c, i )

(a, b): PK, clustered
a: FK to product(a)
c: UQ, FK to main(c)
i: bit-column

I can't find good indexes for the join:

FROM
    product
  JOIN
    lookup
      ON  lookup.a = product.a
  JOIN
    main
      ON  main.c = lookup.c
WHERE
      product.g = 1
  AND
      main.f = 1
  AND
      lookup.i = 1
  AND lookup.b = 17

I tried a covering index on product (g, a, ...) and it's used but not with spectacular results.

Some combinations of indexes on the lookup table produce execution plans with index-merge, with slight efficiency gain over the previous plan.

Is there some obvious combination that I am missing?

Could a re-design of the structure help?

The DBMS is MySQL 5.5 and all tables are using InnoDB.

Table sizes:

product: 67K   ,  g applied:    64K

main:   420K   ,  f applied:   190K

lookup:  12M   ,  b,i applied:  67K


texts:
I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i want to be able to replace all the words apple and orange that exists in table 2 and replace them with ##, is there a query that does this ? my actual table 1 has about 16k words and table 2 has 8 million words. most of the examples on google suggests i type the words to find but that is not possible when i have 16k words I am using mysql workbench and an absolute beginner

(from comment)  table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference


texts:
Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database.

There is currently a table public.users, which has a cast on it to output a record to JSONb.  (e.g. SELECT u::jsonb FROM users u;).

The database has grown in complexity and schemas, which a new schema (think module) has been added that adds a few things to the user accounts (via joins in the schema table).

To make the cast to JSONb consistant across the app, the public.users cast was refashioned to use a cast in a new schema called catalogue.  This way casting public.users will get additional properties from the catalogue schema in a transparent way to the user.  This was done so that the API Server doesn't need to know to use this/that schema, and I can leave my (reused) User API Controller the same.

The new [refashioned] cast returns the cast from catalogue.users, so basically the public's cast function on users call a [SQL] function with the simple SQL command..

CREATE OR REPLACE FUNCTION catalogue.cast_user_jsonb_override(public.user)  RETURNS JSONb STABLE LANGUAGE sql
AS $$
 SELECT u::jsonb FROM catalogue.users u WHERE username = $1.username
$$;

This way the casting public.user to JSONb will utilise the above cast without the user knowing.

However, for some reason calling the cast for public.users is slow, while calling the cast for catalogue.users is fast.

Calling...

EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u;
....
Planning time: 0.046 ms
Execution time: 40.086 ms

while calling...

EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u;
....
Planning time: 0.094 ms
Execution time: 1384.414 ms

or directly...

EXPLAIN ANALYSE VERBOSE SELECT catalogue.cast_users_jsonb_override(u) FROM public.users u;
....
Planning time: 0.101 ms
Execution time: 1325.343 ms

If anything, the catalogue cast should be the slower one as is performs joins and the likes.

The [override] cast function is STABLE to help with performance, but it doesn't seem to do much.

Any ideas as to why calling a function that executes a basic SELECT is slow, while calling the SQL itself is fast? . I haven't tested it in any other postgresql version, and there isn't any priority to do an upgrade to v10 to test as there is currently a deadline on the release.


texts:
I removed the old WAMPServer 2.0 and it left my old MySQL data directory and my.ini intact.
I installed the new WAMPServer 2.2 and need to make my old databases active with the new WAMPServer MySQL.

When I set the data directory in the new my.ini to point to the old data directory, I get this error when trying to start the service:

Could not start the wampmysqld service on Local Computer.
Error 1067: The process terminated unexpectedly.

No other errors. In the Application Event log I see this:

Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

How do I get around this as the old MySQL version was 5.1.36 and the new one is 5.5.16.


texts: Can I use table aliases in a MySQL DELETE statement?

texts:
This does not work:

DELETE FROM topics AS t , posts AS p  USING t INNER JOIN p
WHERE t.id=p.topic_id AND t.id = '5599';

whereas this does:

DELETE FROM topics, posts USING topics INNER JOIN posts
WHERE topics.id=posts.topic_id AND topics.id = '5599';

Is it not valid to use table aliases in a DELETE statement or is there some other way?

My aim is to delete all topics with id 5599 and associated posts with posts.topic_id = 5599


texts:
This is my SQL request :

SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
      d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
      m2_.libelleMention AS libelleMention5, t3_.libelleType AS libelleType6
FROM Bac b0_
INNER JOIN Bachelier b4_ ON b0_.bachelier_cne = b4_.cne
INNER JOIN EtablissementBac e5_ ON b0_.etabBac_id = e5_.codeLieu
INNER JOIN Delegation d1_ ON e5_.delegation_id = d1_.codeLieu
INNER JOIN MentionBac m2_ ON b0_.mentionBac_id = m2_.codeMention
INNER JOIN TypeBac t3_ ON b0_.typeBac_id = t3_.codeType
WHERE m2_.codeMention IN ('TB',  'B')
AND t3_.codeType IN ('114',  '129')
GROUP BY d1_.codeLieu, m2_.codeMention, m2_.libelleMention,
         t3_.codeType, t3_.libelleType, t3_.abbrType
ORDER BY d1_.codeLieu ASC , b0_.mentionBac_id ASC , b0_.typeBac_id ASC

And this is the result (it's a screenshot):

http://nsa33.casimages.com/img/2013/05/10//130510034126658849.png

The thing is I get all the rows with the equivalent number (sclr0), but I also need rows with null values. The needed rows are defined by the columns libelleMention5, libelleType6. In other words, I want for each codeLieu1, all the possible combinations of libelleMention5 and libelleType6, and the equivalent number in the column sclr0 (which should be 0 if the cell is NULL).

How can I do that ?

EDIT

This is the new screenshot : http://nsa34.casimages.com/img/2013/05/11//130511101655302101.png


texts: How we can clear tempdb without restart MSSQL service

texts:
While checking my tempdb details, its showing that almost 90 GB is consumed. So I tried clearing all caches and perform shrinking, but still it's consuming 90 GB of space.

My drive became full where tempdb is allocated, which is why I am trying to get space from database files.

I have executed following queries to resolve issue. But it is still consuming 90 GB.

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file

The tempdb database has only one .mdf and one .ldf file.

Is there any option to clear tempdb without restarting MSSQL service?

Note: MSSQL Version 2012, tempdb is in simple recovery mode.

Update : While checking the Messages of shrinking It is showing
'DBCC SHRINKFILE: Page 1:12185400 could not be moved because it is a work table page.'
I hope its due active connection to tempdb, So i checked sp_whoisactive its not showing any connection. But replication is going on the server

Also i got table wise size from tempdb as follows

How i can shrink 'tempdb' properly ?

A


texts: How I should design tables for transaction (payment) for banking?

texts:
My team and I are trying to design a new simple database system to manage transaction of customers who use their debit cards. These are some problems we haven't yet decided:

When it comes to 'payment by card' process, there should be a transaction to deal with it, there might be pending status before the process will have completed. So it is necessary to create 2 separate tables, one of which manage the transaction status from that payment , and the other only about the successful result after the transaction completes. Or is there any other way around?

We have considered a 'isPending' field (bollean or bit) should be added but since we have not decided which table should contains it so it is still a problem. But anyway, assuming that the tables have completed, should this filed only update (.e.g from true to false, 1 to 0,etc) or there should be a new records should be created?

We hope that our questions do not confuse you. Please give some recommendation to help our system more improved! Thank you! (We just hope that we do not post in the wrong section, so if that was the case, we would be very sorry)


texts:
I 'm trying to configure alerts for replication. Currently on my test setup I run everything under user 'sa'. When I try to set up alert for agent failure I get the following error

Login failed for user 'sa'.

This error is shown when i try to add a new operator.

Any suggestions how to fix this problem.
thanks


texts: How to get a boolean value into a SELECT operation based on another table?

texts:
I have two tables: Message and Attachments (which represent entity types that have a one-to-many relationship) as follows:

CREATE TABLE message (
    messageId ...,
    [from]...,
    [to]...,
    subject...,
    body...
);

CREATE TABLE attachments (
    messageId...,
    filename...
);

I want to SELECT the basic message data and include a bit column to denote whether the message has attachments or not. I am trying to use EXISTS in the operation shown below:

SELECT
M.*,
hasAttachments = EXISTS(SELECT AttachmentId
                          FROM Attachment
                         WHERE messageId = M.messageId),
FROM Message M

But this doesn't work - What's the way to do what I'm looking for?


texts: Allocating 8GB memory to MySQL on a 64bit system

texts:
Specs -

MySQL 5.0.x  ,  Redhat 5.9  ,  Physical memory - 16GB

I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error -

InnoDB: Error: cannot allocate 8589950976 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 37679104 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.

Here is output of free -m -

             total       used       free     shared    buffers     cached
Mem:         15922      15269        653          0        248       4275
-/+ buffers/cache:      10744       5178
Swap:         2047          0       2047

Here is output of ulimit -a -

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 127352
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 127352
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

I checked file '/etc/security/limits.conf'. Nothing in there. All lines are commented (start with #). Checked directory '/etc/security/limits.d/', empty.

Something is preventing allocation of more than 4GB of memory to MySQL. Not sure what.

Any ideas?

Danke.


texts: MySQL: is there a way to make a self-referencing join recursive?

texts:
I need to insert some records derived from relationships in the same "pivot" table.

We have a products_to_products table which stores relationships between products.
Here's a simplified example (also available in this fiddle here).

CREATE TABLE products_to_products
    (`left_id` varchar(4), `right_id` varchar(4) );
INSERT INTO products_to_products (`left_id`, `right_id`)
VALUES
    (1234, 'aaaa'),
    (1234, 'bbbb'),
    (1234, 'cccc'),
    (5678, 'dddd'),
    (5678, 'eeee'),
    (5678, 'ffff'),
    (1011, 'gggg'),
    (1011, 'aaaa'),
    (1011, 'dddd');

What I need to do is insert new rows which express the relationships found there.
For example, 1234 and 1011 are related on aaaa.
Likewise for 5678 and 1011 because they relate on dddd.

Easy enough to collect with something like:

SELECT DISTINCT
  l.left_id AS left_id,
  r.left_id AS right_id
FROM products_to_products AS l
JOIN products_to_products AS r
  ON l.right_id = r.right_id
WHERE l.left_id <> r.left_id

...but then, that doesn't include the fact that 1234 and 5678 would now relate on 1011.

Is there a way to make that select recursive, or am I just going to have to run my queries over and over again to keep picking up the relationships created by the previous insert query?

NOTE: I'm better at reading SQL than thinking SQL, so please feel free to mention any other optimizations/improvements that might be worthwhile :)


texts: Table design for “Users & Groups” plus “Groups within Groups”

texts:
I need to represent users and groups, where groups can be nested within other groups.

(Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its implementation details, only its user documentation.)

Putting a group into other groups creates a hierarchy. The users of one group placed in another group gains the access privileges of both groups. For example, if you place the Executives group in the Accounting group, the users of the Executives group enjoy the privileges of both groups. However, users placed only in the Accounting group benefit from the privileges of that group only. (Admittedly, not a likely realistic business scenario, just a contrived example.)

Something like this ERD diagram, I suppose.

I know how to handle a Many-to-Many relationship of user_ to group_. But I have no idea:

How to represent the nested group-within-group, where a group may belong to zero, one, or more than one other group.
How to query “Is this group X in group Y”.
How to query “Is this user Bob in group X” where “group X may be either directly assigned to that user or inherited via group-within-group”.

Is this group-within-groups considered a “recursive” relationship? What is the correct technical term?

Possibly related: Recursive query in SQL Server and Recursive SQL query to identify


texts: Required values for the performance counters

texts:
That will sound ludicrously, by the accident I became a 'DBA' and have to handle one big database around 2TB data. I would like to know what are optimal values for the following performance counters:

Average Disk sec/Transfer
Current Queue Length D
Disk Bytes/sec
Transfer/sec
PLE

In case of PLE I have read that in old times it was 300 sec per 4GB of RAM. We have 128GB of RAM, and PLE fluctuates from 0 to 15k. Anyway, I don't think it is possible to have high PLE value when everyday around 50-70GB of new data is coming in, same amount is being deleted and we store data from last 30 days, so users can read and manipulate it


texts: Freeze I/O on purpose like a vss snapshot

texts:
I want to freeze the I/O of a SQL Server instance like the shadow copy does.

The result should look like this:

2020-01-19 23:11:40.90 spid73      I/O is frozen on database prod. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

2020-01-19 23:11:57.89 spid73      I/O was resumed on database prod. No user action is required.

But I want to decide when the I/O is resumed. I tried it myself with a backup, but it finished too fast.

I want to debug my application during this I/O freeze.

How can I achieve this behavio(u)r?

Edit - 2020-01-27 10:53

The purpose is that the SQL Server doesn't accept new connections during this time:

Exception:
Class name:    EMSError
Message:       [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Unit name:     OLEDBAccess


texts: Update value equal to the count appearance on the last X records

texts:
I have a MySQL table everyday time a new value is inserted I need to count look at the last 100 previous records and count how many times the inserted value appears.

You can assume the structure of my table is:

num (int, primary, auto-increment) | value (varchar) | last_100_repetitions (int)

I need to update last_100_repetitions based on how many times value appears on the last 100 inserted records (where num indicates the order of the records).


texts: Add column to show rows with close time differences without cursor


texts: How to restrict CPU consumption of CPU intensive applicaiton with Db2 Workload Manager?

texts:
in Db2 v11.1 on Linux I would like to have control over applications that consumes too much CPU resources.
For every application that consumes too much CPU for more then 3 seconds I want to lower CPU resources to max 5%.
In Db2 Workload Manager I created two subclasses. First without restrictions and second with 5%-CPU restriction.
The trouble I have now is at "create threshold" command (bellow). CPUTIMEINSC actually means time spend in sub-class,
but I am looking for option to only trigger threshold when SQL consumes a lot of CPU power.
How to restrict CPU consumption to "aggressive" applications?
Regards
-- Create super-class.
CREATE SERVICE CLASS SC_APPS;

-- Create default subclass that all applications start with.
CREATE SERVICE CLASS SC_APPS_100 UNDER SC_APPS SOFT CPU SHARES 1000 CPU LIMIT 100 PREFETCH PRIORITY DEFAULT BUFFERPOOL PRIORITY DEFAULT;

-- Create subclass with CPU, disk I/O and memory restrictions.
CREATE SERVICE CLASS SC_APPS_05  UNDER SC_APPS HARD CPU SHARES   50 CPU LIMIT   5 PREFETCH PRIORITY LOW    BUFFERPOOL PRIORITY LOW;

-- Move application that consumes CPU for more then 3 seconds from non-restricting subclass to resource restricting subclass
CREATE THRESHOLD TR_APPS_GOTO_05
    FOR SERVICE CLASS SC_APPS_100 UNDER SC_APPS ACTIVITIES
    ENFORCEMENT MEMBER
    WHEN CPUTIMEINSC > 3 HOURS CHECKING EVERY 1 SECOND
        REMAP ACTIVITY TO SC_APPS_05;

-- Crete workload restrictions for one user only
CREATE WORKLOAD WL_APPS_05  SYSTEM_USER ('USER1') SERVICE CLASS SC_APPS_05  UNDER SC_APPS;

-- Grant access to public
GRANT USAGE ON WORKLOAD WL_APPS_05 TO PUBLIC;


texts: Relation between lossy decomposition and Natural join?

texts:
I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables with common attribute results formation of spurious table. the problem is, both have common attribute but showing 2 different solutions.one is showing original tuple other a spurious tuple.

Please Help.

I've seen these articles:

https://stackoverflow.com/questions/12671362/lossy-decomposition https://stackoverflow.com/questions/14548543/natural-join-if-no-common-attrubute http://en.wikipedia.org/wiki/Relational_algebra#Natural_join_.28.E2.8B.88.29


texts: Should select queries ever take longer than 1 second?

texts:
I have select queries in my slow-log file that take 40-120 seconds.

When I consider something like a Google search, how fast that is, it makes me re-evaluate my entire database structure.

Since Google is literally searching billions of URLs in factions of a second, should I be worrying about 40 second queries?

# Query_time: 58.143590  Lock_time: 0.023085 Rows_sent: 3  Rows_examined: 1092421
use scrapers;
SET timestamp=1450286867;
SELECT * FROM `apps` WHERE dev_name = '' LIMIT 10;


texts: Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4

texts:
SQL Server Standard 2005 sp2 32bit running on Windows 2003 Standard 32bit service pack 2.

I attempted a service pack upgrade from sp2 to sp4 using SQLServer2005SP4-KB2463332-x86-ENU.exe and received the following error:

Property(S): UpgradeInstruction = Connect to SQL instance MYSERVER as sysadmin
Launch SQL statement USE master
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\sysdbupg.sql
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\repl_hotfix_install.sql
Stop service MSSQLSERVER
Property(S): SqlUpgradeMessage = SQL Server Setup failed to parse the SQL script "d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql". The error code is The system cannot find the file specified.
. To continue, correct the problem, and then run SQL Server Setup again.

The file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql is missing and I have tried this upgrade from two different SQLServer2005SP4-KB2463332-x86-ENU.exe files.

After the service pack install completed, I was able to start SQL Server and the version was 9.0.5000.  There were no other errors in the logs, but I did rollback the server using a vmware snapshot taken before installing SP4.

I googled around but didn't find anything useful.

Why would the file DbEngine_hotfix_install.sql file be missing from the service pack installation ?