texts: Configure listen_addresses automatically in custom installation

texts:
I have included Postgres in a custom installer for a Windows application. I do not use the installer binary, but extract the Postgres zip archive for installation and then run initdb from a batch script.
After installation, the db cluster should accept connections from everywhere. Currently my installation routine

modifies pg_hba.conf
creates a firewall rule

Problem: I don't know how make the installation routine configure postgresql.conf so that listen_addresses is set to the right value:
listen_addresses = '*'

Currently this has to be done manually. I didn't find a parameter in initdb that does this.
Question: Is there any way to automate the installation process so that listen_addresses is configured as needed? Can this be done using initdb?


texts: Why is PostgreSQL not using composite Index on JSONB Fields?

texts:
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (j2->>'f2') IS NULL
Since I have millions of records, and queries started to take long minutes, I created a composite index:
CREATE INDEX "f1_f2" ON table (((j1->>'f1')::int), (j2->>'f2'));
However, this didn't really help. Looking at EXPLAIN, I see that Seq Scan is still being used.
Is this because PG does not handle composite JSONB fields? Any other reason that I should check?
As requested, I am attaching the complete execution plan. Thank you!
EXPLAIN (ANALYZE, buffers, format text) SELECT id FROM table WHERE (j1->>'f1')::int = 1 AND (j2->>'f2') IS NULL;

"Gather  (cost=1000.00..1551479.43 rows=29850 width=19) (actual time=0.617..354008.670 rows=2949996 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=53872590 read=7841241 dirtied=668 written=15597"
"  ->  Parallel Seq Scan on table (cost=0.00..1547494.43 rows=12438 width=19) (actual time=8.702..353420.358 rows=983332 loops=3)"
"        Filter: (((j2->> 'f2'::text) IS NULL) AND (((j1 ->> 'f1'::text))::integer = 1))"
"        Rows Removed by Filter: 5821066"
"        Buffers: shared hit=53872590 read=7841241 dirtied=668 written=15597"
"Planning Time: 0.155 ms"
"Execution Time: 354200.845 ms"

I am also adding the SQL generated for the index using pgadmin's "CREATE script" function:
-- Index: f1_f2

-- DROP INDEX public.f1_f2;

CREATE INDEX f1_f2
    ON public.table USING btree
    (((j1->> 'f1'::text)::integer) ASC NULLS LAST, (j2 ->> 'f2'::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

After SET enable_seqscan=off, I got the following EXPLAIN. Note that in addition to the composite index, I also have an index on each field. I see that the plan used the single indexes but not the composite one.
"Gather  (cost=171490.09..1957279.19 rows=35622 width=19) (actual time=3346.873..132192.242 rows=3526839 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=8524759 read=1998657 dirtied=331 written=7043"
"  ->  Parallel Bitmap Heap Scan on table (cost=170490.09..1952716.99 rows=14842 width=19) (actual time=3310.690..131586.964 rows=1175613 loops=3)"
"        Recheck Cond: (((j1->> 'f1'::text))::integer = 1)"
"        Rows Removed by Index Recheck: 1801933"
"        Filter: ((j2->> 'f2'::text) IS NULL)"
"        Rows Removed by Filter: 1211720"
"        Heap Blocks: exact=18098 lossy=349995"
"        Buffers: shared hit=8524759 read=1998657 dirtied=331 written=7043"
"        ->  Bitmap Index Scan on f1 (cost=0.00..170481.18 rows=7124349 width=0) (actual time=3335.250..3335.250 rows=7167075 loops=1)"
"              Index Cond: (((j1->> 'f1'::text))::integer = 1)"
"              Buffers: shared hit=4 read=20205 written=809"
"Planning Time: 0.160 ms"
"Execution Time: 132381.519 ms"


texts:
When we use for xml clause to create xml from a row like as follows

select * from Menus where MenuID=100 for xml path('')

The xml will miss the columns which have NULL as the value.

I have a found method like

select * from Menus where MenuID=100  for xml path('') , ELEMENTS XSINIL

But the xml will create as follows only

<MenuID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">100</MenuID>
<MenuOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1</MenuOrder>

Is there any way to create like as follows with NULL columns

<MenuID>100</MenuID>
<MenuOrder>1</MenuOrder>


texts: Workbench freezes on connecting after Ubuntu 18.04 upgrade

texts:
I recently upgraded to Ubuntu 18.04 and started having this issue, I can connect to my local mysql server via the terminal fine using

mysql -u root -p

however when I try to use mysql workbench it just gets stuck on connecting and wb.log displays the following:

11:39:07 [INF][   WBContext UI]: Initializing workbench context UI with these values:
    base dir: /usr/share/mysql-workbench
    plugin path: /usr/lib/mysql-workbench/plugins
    struct path: /usr/share/mysql-workbench/grt
    module path: /usr/lib/mysql-workbench/modules
    library path: /usr/share/mysql-workbench/libraries
    user data dir: /home/<REPLACED USERNAME>/.mysql/workbench
    open at start:
    open type:
    run at startup:
    run type:
    Force SW rendering: No
    Force OpenGL: No
    quit when done: No
11:39:07 [INF][      WBContext]: WbContext::init
11:39:08 [INF][            WBA]: Looking for extension modules for WBA...
11:39:08 [INF][            WBA]: 0 extension modules found
11:39:08 [WRN][            grt]: /usr/share/mysql-workbench/modules/data/mysql_rdbms_info.xml:1401: link 'om.mysql.rdbms.mysql.driver.native_sshtun' <object > key=owner could not be resolved
11:39:08 [WRN][            grt]: /home/<REPLACED USERNAME>/.mysql/workbench/connections.xml:27: link '6d8ecd74-acad-11e8-9c35-408d5cdbf11f' <object GrtObject> key=owner could not be resolved
11:39:08 [INF][      WBContext]: System info:
    MySQL Workbench Community (GPL) for Linux/Unix version 8.0.12 CE build 13312926 (64 bit)
    Configuration Directory: /home/<REPLACED USERNAME>/.mysql/workbench
    Data Directory: /usr/share/mysql-workbench
    Cairo Version: 1.15.10
    OS: Ubuntu 18.04 x86_64
    CPU: 8x Intel(R) Xeon(R) CPU E3-1230 v5 @ 3.40GHz (3700.971MHz) - 15.62GiB RAM
No video adapter info available
    Distribution: Ubuntu 18.04.1 LTS

    Fips mode enabled: no

11:39:08 [INF][    WQE backend]: Found auto-save workspace /home/<REPLACED USERNAME>/.mysql/workbench/sql_workspaces/local-1.autosave
11:39:09 [INF][SQL Editor Form]: Opened connection 'local' to (Ubuntu) version 5.7.23-0ubuntu0.18.04.1

When I edit the connection in mysql workbench and click "Test Connection" i get the following result:

So here is a few things I have tried so far:

Completely purged mysql-server, mysql-commons, mysql-client etc.
Reinstalled mysql-server
Logged into the mysql server via terminal and modified the plugin for root user to use mysql_native_password
Tried creating a new custom user and tried logging into that, once again it works via terminal but not via MySQL Workbench.
Ran apt update, apt upgrade
Downloaded latest MySQL Workbench from their website and installed that.

It wouldn't even allow me to connect to a fresh db install after mysql server reinstall.

These are the versions I'm working on:
MYSQL Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)
Linux Kernel Version: 4.15.0-33-generic
Distribution: Ubuntu 18.04.1 LTS
MYSQL Client Version: mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper


texts: Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

texts:
MSDN advises that @@FETCH_STATUS be used with care because it's global:

  Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor.

This leads me to two questions:

Is it possible that another (not nested) cursor could run concurrently with mine and affect the value of @@FETCH_STATUS between my FETCH NEXT and WHILE condition? For example, say the other cursor is in a stored procedure that's called on a schedule.
If this is possible, does @@FETCH_STATUS have an equivalent of @@IDENTITY's SCOPE_IDENTITY()?


texts:
We are storing a list of images in a table in an image column. These are stored as their raw image size so they can be viewed in their details at full resolution.

The problem I have is I want to show them in a listing in our application and I just want to show them as thumbnails. When I retrieve them via a view our entityFramework code bombs with out of memory errors trying to resolve all the images into fields in the code.

I'm wanting to know whether I can resize the image column at the database view level before it hits the application. i.e. In our view have a function that takes the full size image and resizes it to 80x80 and returns it. e.g.

Select fxShringImage(EntityImage) From EntityImage

Any ideas? Is this possible?


texts: Changing listener IP on default TCP endpoint via DDL statement

texts:
I'm running SQL server instances that are listening on all available interfaces (0.0.0.0). I need them to listen on one specific IP only. I also need to automate this configuration change, e.g. via a DDL statement.

My strategy is to use ALTER ENDPOINT to change LISTENER_IP on the default TDS endpoint.

I ran SELECT * FROM sys.endpoints and discovered the TSQL Default TCP endpoint.

Then I executed the following SQL command:

ALTER ENDPOINT "TSQL Default TCP"
AS TCP (
    LISTENER_IP = 192.168.4.22
)

This produced the following error.

Msg 7871, Level 15, State 1, Line 1
The clause 'AS TCP' is not valid for this endpoint type.

This doesn't make sense to me. It is definitely a TCP endpoint.


texts: How to do a configuration on RMAN with run block without save this configuration on rman

texts:
I want to do a run block with this configuration for test:

run {
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50M;
BACKUP DATABASE;
}

But I don't want that the configuration CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50M stay save on rman after of run the run block, how to do it?


texts: In SSIS, how to force an open connection to to SQL Server while package is executing (hint: its not retainsameconnection=true)

texts:
We have an SSIS package with an OLE DB connection to a SQL Server database.  The database restores every 15 min from log shipping unless a connection is open.  When the SSIS package runs (takes several hours), the pacakge will periodically fail with this message:
Database 'XXXXX' cannot be opened. It is in the middle of a restore.
We thought that setting retainsameconnection=true in the connection manager would keep the connection open during the duration of the package execution, but it doesnt seem to work like that.
Question is:  Is there a setting/technique in ssis to force an open connection during the duration of an ssis pacakge being executed?


texts:
Our mongodb relication has many logs like this:

>     2017-11-24T02:15:50.679+0000 I ACCESS   [conn6537] Successfully authenticated as principal stats on stats
>     2017-11-24T02:15:50.679+0000 I ACCESS   [conn6535] Successfully authenticated as principal stats on stats
>
>     2017-11-24T02:15:51.150+0000 I -        [conn6535] end connection :49982 (13 connections now open)
>     2017-11-24T02:15:51.150+0000 I -        [conn6536] end connection
>
> 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1] connection accepted
> from 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1] connection
> accepted from 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1]
> connection accepted from

Our config:

db.getLogComponents();

{
        "verbosity" : 0,
        "accessControl" : {
                "verbosity" : -1
        },
        "command" : {
                "verbosity" : -1
        },
        "control" : {
                "verbosity" : -1
        },
        "executor" : {
                "verbosity" : -1
        },
        "geo" : {
                "verbosity" : -1
        },
        "index" : {
                "verbosity" : -1
        },
        "network" : {
                "verbosity" : -1,
                "asio" : {
                        "verbosity" : -1
                },
                "bridge" : {
                        "verbosity" : -1
                }
        },
        "query" : {
                "verbosity" : -1
        },
        "replication" : {
                "verbosity" : -1
        },
        "sharding" : {
                "verbosity" : -1
        },
        "storage" : {
                "verbosity" : -1,
                "journal" : {
                        "verbosity" : -1
                }
        },
        "write" : {
                "verbosity" : -1
        },
        "ftdc" : {
                "verbosity" : -1
        },
        "tracking" : {
                "verbosity" : -1
        }
}

How can I disable these logs? Thank you!


texts:
I am wanting to see a list of codes with their relevant chapters in my database.  A code can have more than one chapter assigned, therefore meaning more than one row in the database.

This is how the database looks:

Code     |     Chapter
000      |     1.1.0
000      |     3.0.0
123      |     1.1.0
123      |     1.2.0
456      |     1.7.0
789      |     1.2.0
789      |     2.1.4
789      |     5.1.7

I would like to do a STUFF function to display the Code and the Chapter/s on one line, but for every Code in the system.  This is how I would like it to look:

Code     |     Chapter
000      |     1.1.0, 3.0.0
123      |     1.1.0, 1.2.0
456      |     1.7.0
789      |     1.2.0, 2.1.4, 5.1.7

But I am receiving the following:

Code     |     Chapter
000      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
123      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
456      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
789      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7

The SQL I am using is:

select distinct
       C.Code,
       STUFF((SELECT ', ' + CHAP.[Key]
             FROM Chapters CHAP
             ORDER BY CHAP.[Key]
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C
left outer join CodeChapterLinks CCL
ON C.Id = CCL.CodeId
left outer join Chapters CHAP
ON CCL.ChapterId = CHAP.Id
ORDER BY C.Code asc

Any assistance is much appreciated :)


texts:
I've a MySQL table that looks like the following:

http://sqlfiddle.com/#!2/ca67ac/1

I use it to build a grid where i put my content. The resultant is this:

I've to render every row separatelly but, inside every row, render it's content by column.
So, for this task i need to get the rows in this order: 1, 2, 3, 4, 6, 5, 7.

column number 6 must come before 5 because it's first in the column order inside that row from left to right.

Is there any sorting method that i can use to achieve that? Order by row but order by column inside that order.


texts: max_connections error during restoring PostgreSQL to Local Database

texts:
I am facing max_connections error during database restoration on ApsaraDB for PostgreSQL in a local database.

I am using below mentioned command for database restoration

\i c:/mydb1.sql


texts: Extending fiat transaction log to support cryptocurrency

texts:
Let's assuming that I have an existing database that retaings fiat currency transaction as described below with decimal (18, 4).

Now, I would like to record bitcoin, ethereum and erc20 token transactions.

Transaction
------------------
TransactionID
CurrencyID
UserID
DebitAmount  decimal(18, 4)
CreditAmount decimal(18, 4)

The decimal precision of cryptocurrency is suggested as follows:

 decimal (16, 8)  -- bitcoin
 decimal (30, 18) -- ethereum / erc20 token

I am thinking of just:

Treating bitcoin, ethereum, erc20 token as new currency in the Currency table.
Changing the DebitAmount and CreditAmount directly to decimal(30, 18).
Applying decimal(30, 18) data type changes to all the tables that retain currency amount.

My questions are:

Any possible impact to original currency amount conversion that I need to look after for when I increase the precision (eg. currency rate change DebitAmount * 1.023)?
Am I doing this in the "correct" way?
Is there any other better approach/alternative to this?


texts:
Possible Duplicate:
  Files - in the database or not?

anyone can tell me, what is the best place for save binary file? database or just in a file system? and what is plus and minus for both? :D

actually, I want to save my image file, but I don't know what is the place for save my image

thanks :D


texts: Cast string to decimal without rounding the decimal part

texts:
I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don't want that, I just want that it keeps all the decimals that the original table had.

Here is the query I'm using to populate this new table:

UPDATE "Products" p
SET "conversionFactor" = (SELECT cast(a.factor_conversion as decimal)
                            FROM db2.articulo a
                            WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)
 WHERE EXISTS(SELECT 1
                FROM db2.articulo a
                WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)

In Products if I select that id I get: 327.58 but in the original table it is 327.580134

How can I fix that? Also other rows can have the same amount of decimals, others less, others more.

The datatypes of "conversionFactor" and factor_conversion are both strings in table articulo. I want them to be decimals in the Products table without rounding any decimals.


texts: SQL Server replication issue - table not updating

texts:
I have a transactional replication publication from the primary server (SQL Server 2008 R2, with the DB in 2000 compatibility) to a read-only copy used for reporting/spreadsheets/etc. Replication is primarily log-based with snapshots on-demand. This publication includes an article for a  table of company data (basic stuff: name, address etc).

It was brought to our attention this morning that the data in this table was not being updated on the subscriber when changed on the publisher. We verified that, yes, the table was missing some newly-added rows. Generating a new snapshot fixed that, but further tests show it's still not updating real-time based on the transaction log.

Replication is running, and Replication Monitor is showing data flow between servers with just a few seconds' latency, and so insists that nothing is wrong. The oddest thing is that when I ran a validation on the subscription, I didn't get any result, pass or fail, for the company table (all other tables in the publication passed). It's almost as if the replication process is completely ignoring the table, despite its inclusion in the publication.

The only other clue is that when we generated the new snapshot, the company table errored due to an unsupported column type (varchar(max), not supported by 2000). We fixed the issue by allowing the snapshot to change the affected columns to text types, then manually changing them back, which is a common workaround that's worked fine before. However, replication was recently reinitialized (couple weeks back) for this database between the two servers to add some new tables to the publication, which involved another snapshot generation. There was no problem with that table for that snapshot, and nothing's changed on it since. In fact, the columns that needed to change to legacy types have been in place long before any replication issues were reported.

This one's really baking our noodles; any help tracking down the issue would be appreciated.

EDIT: My sharp-eyed boss caught a message on the subscription status window that the initial snapshot specifically for the company table article wasn't present. We generated another snapshot, and problem solved.


texts: Understanding why Replication Distribution Agent stops

texts:
Let me preface this in that I'm working as an accidental DBA at the moment.

We have continuous transactional replication running.  For some reason the Distribution Agent stops with a message of "success", but I can't understand at all why it is stopping and from the logs I don't get much information.

Two questions.

Other than looking at the monitor does anyone have some good ideas as to where I can get a bit more detail?  I can do verbose logging to a file but the replication has lots of data and the files grow really big really fast.
Is there a way to have the Distribution Agent restart itself whenever it stops other than when I manually stop it?  Maybe adding an additional step to the Agent job?


texts:
I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice with a short interval in between, the query only takes about 90 ms. The query is in both cases the same. When I run the script, with the same query, after some time again, it takes 1500 ms again.

Why is that? Does the database cache automatically? Is there some time the database saves the cache and then automatically deletes it?

I assume the results can't be cached by PHP, because this happens in two different threads. I wouldn't think PHP would cache the results, because it can't know if the database has changed.

I have a script running every minute to insert new rows to the database. This might also be the reason that it takes 1500 ms again after some time; the cache would've been deleted, because the relevant tables aren't the same anymore.


texts: What is the minimum privilege required to create a full text index?

texts:
I just created a full text catalog on my database.  I now have a developer who needs the ability to create full text indexes on the tables in this database.

The suggestion given in a previous question was to grant db owner to the user. I'd really like to avoid giving such a high level of access.

Is there any way to do this without granting dbo? I wasn't able to find anything regarding the permissions aspect on BOL.


texts: Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

texts:
Informix 11.70.TC4DE:

CREATE TABLE cluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO cluster_tbl;

CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id);

{###}

CREATE TABLE noncluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO noncluster_tbl;

CREATE UNIQUE INDEX ncl_idx ON noncluster_tbl(fk_id);

{###}

UPDATE STATISTICS;

It's my understanding that a cluster index is essentially the same as pre-sorting the load file, inserting it into the table and creating the index for the foreign key column.

I created two tables, each loaded with 2 Million rows of identically data, pre-sorted by the foreign key. However, one table has a cluster index on fk_id, the other table with a non-clustered index on fk_id.

Is there any difference in the tree structure or additional overhead in maintaining or accessing data from a table with a cluster index, versus the same table/data with a non-cluster index?


texts: Copy table from one server to another including keys and indexes

texts:
I have been working on a small project for about two weeks.

We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live databases....kind of like a DIY mini-data warehouse.

We have three SQL Server 2008 remote servers and I am compiling the data onto one local server running three separate SQL Server Express 2017 instances.

The SQL Server Express databases are all set up, and I have successfully tested copying the tables required using several different methods including:

SSMS Import/Export Wizard
as well as scripting using Linked Servers from the new database:

INSERT INTO dbo.table
  SELECT *
  FROM remoteserver.remotedatabase.dbo.remotetable

Both of these successfully copied tables and data BUT not keys, indexes, and other constraints.

I am only recently working in databases, so I am at a loss myself, and Google yielded the above suggestions for copying, but I cannot find anything that includes keys, indexes and other constraints when transferring tables BETWEEN servers.


texts:
I am able to access a database using the Windows Authentication method in DBeaver. However, when I switch to different account in Windows and I would like to access the datebase, I used SQL Server Authentication method, it keeps giving me error. How should be the username format?

Moreover, how can I add another Windows account to the database so I access the database without the need to switch the Windows account everytime.


texts: Is there a name for this database schema of key values?

texts:
We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute):

Before: one column per attribute

ID   Ht_cm   wt_kg   Age_yr  ...
1      190      82     43    ...
2      170      60     22    ...
3      205      90     51    ...

After: one column for all attributes

ID    Metric   Value
 1     Ht_cm     190
 1     Wt_kg     82
 1     Age_yr    43
 1      ...
 2     Ht_cm     170
 2     Wt_kg     60
 2     Age_yr    22
 2     ...
 3     Ht_cm     205
 3     Wt_kg     90
 3     Age_yr    51
 3     ...

Is there a name for this database structure?  What are the relative advantages?  The old way seems easier to place validity constraints on specific attributes (non-null, non-negative, etc.) and easier to calculate averages.  But I can see how it might be easier to add new attributes without refactoring the database.  Is this a standard/preferred way of structuring data?


texts: How do I switch off SCHEMABINDING for a view without recreating it?


texts: Precedence constraint does not run in SSIS package

texts:
Hope you are doing well.
I have a Precedence constraint that does not work . Putting it into more clear way ,  the package is executed successfully up until the components in which I have the precedence constraint as you can see here:

I call a procedure in the component before the "precedence and constraint" component and when I execute the procedure in SSMS it gives me the correct result . But I have no  idea why it does not work in here.
Here are some other pictures related to this issue:


texts: Is there a standard methodology for determining how much CPU, memory and disk space is needed to support an Oracle database with 500 million rows of sensor data (and counting) ? The system in question is a SCADA database gathering sensor data from hundreds of field devices on a daily basis. The same database is used for reporting and analysis (a watershed and wastewater management system for a major municipality).


texts: oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

texts:
I have a Merge query (open to better suggestions) that seems to cause the server to hang.  (I'm not sure what it's doing, but it pegs the CPU and never completes)  When I run it through the query tuner, I'm told it's caused by "an expensive cartesian product operation was found at line ID 7 of the execution plan", and that I should "consider removing the disconnected table or view from this statement or add a join condition which refers to it."  The problem is, there is no disconnected table.

Here's the explain plan:

---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |               |     1 |    18 |    75  (10)| 00:00:01 |
|   1 |  MERGE                            | CM_SSS_DETAIL |       |       |            |          |
|   2 |   VIEW                            |               |       |       |            |          |
|   3 |    SORT GROUP BY                  |               |     1 |  1352 |    75  (10)| 00:00:01 |
|   4 |     NESTED LOOPS                  |               |       |       |            |          |
|   5 |      NESTED LOOPS                 |               |     1 |  1352 |    74   (9)| 00:00:01 |
|   6 |       NESTED LOOPS                |               |     1 |  1328 |    73   (9)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN       |               |     1 |  1306 |    72   (9)| 00:00:01 |
|   8 |         TABLE ACCESS FULL         | CM_SSS_DETAIL |     1 |  1274 |    18   (0)| 00:00:01 |
|   9 |         BUFFER SORT               |               | 12815 |   400K|    54  (12)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL        | CI_CC         | 12815 |   400K|    54  (12)| 00:00:01 |
|  11 |        TABLE ACCESS BY INDEX ROWID| CI_CASE       |     4 |    88 |     1   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN          | XT220S1       |     6 |       |     1   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN            | XT222P0       |     1 |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID  | CI_CASE_CHAR  |     1 |    24 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - filter("CC"."CC_TYPE_CD"='1NAA-LTR' OR "CC"."CC_TYPE_CD"='1NEA-LTR' OR
"CC"."CC_TYPE_CD"='1NIP-LTR' OR "CC"."CC_TYPE_CD"='CBS-LTR' OR
"CC"."CC_TYPE_CD"='CBS1-LTR' OR "CC"."CC_TYPE_CD"='CBS2-LTR' OR
"CC"."CC_TYPE_CD"='DNAR-LTR' OR "CC"."CC_TYPE_CD"='INR-LTR' OR "CC"."CC_TYPE_CD"='IPL-LTR'
OR "CC"."CC_TYPE_CD"='SOFB-LTR')
12 - access("CC"."PER_ID"="C"."PER_ID")
13 - access("CHR"."CASE_ID"="C"."CASE_ID" AND "CHR"."CHAR_TYPE_CD"='OBLGID')
14 - filter("D"."SA_ID"="CHR"."CHAR_VAL_FK1")

-------------------------------------------------------------------------------

Here's my query:

MERGE INTO cm_sss_detail d
USING (
    SELECT chr.char_val_fk1, MIN(cc.cc_dttm) first_date
    FROM ci_case c
    JOIN ci_cc cc
        ON (cc.per_id = c.per_id)
    JOIN ci_case_char chr
        ON (chr.case_id = c.case_id)
    WHERE chr.char_type_cd = 'OBLGID'
        AND cc.cc_type_cd IN (  '1NAA-LTR','CBS1-LTR','CBS2-LTR','CBS-LTR',
                                'DNAR-LTR','IPL-LTR','INR-LTR','SOFB-LTR',
                                '1NEA-LTR','1NIP-LTR')
    GROUP BY chr.char_val_fk1
) b
ON (d.sa_id = b.char_val_fk1)
WHEN MATCHED THEN UPDATE
    SET d.fst_bill_date = b.first_date;

The cm_sss_detail table has an index on the sa_id column (it's also the PK).


texts: MySQL DB design for very long growing row size

texts:
My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research.
The number of columns grows more than 1000 now and I got this error:
 Warning: (139, 'Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.')

I read this post but increasing the page size would not help because the row size is growing and it will exceed anything at the end.
I want to restructure the table but I don't have a good idea. How can I restructure this table?

P.S. As a reference, it's time-series data and I put the timestamps as the index. And the columns can be grouped into categories and each group size grows at the same time.

P.P.S I add more details. There are a lot of columns and I decided to combine all of them into one table because each column shares the same timestamps. In addition, to track the identity of column, I made one extra table describing what the column is about.
This is the part of the data table:
+----------------------------+-----------+---------------+--------------------+
| etstamp                    | 1         | 2             | 3                  |
+----------------------------+-----------+---------------+--------------------+
| 2019-01-01 13:53:30.000000 |   3802325 |  -12298828971 |  0.964893760996075 |
| 2019-01-01 14:01:00.000000 | -13088926 |  -12298828971 |  -1.33234100135318 |
| 2019-01-01 14:06:00.000000 | -13088926 |   -5892372191 |  0.343203392148817 |
| 2019-01-01 18:35:30.000000 |  -3751101 |   -4074909927 |  -1.28552097428958 |
| 2019-01-01 23:19:00.000000 |  40493616 |   90751800131 |   1.41623954680335 |
| 2019-01-01 23:20:30.000000 |  40493616 |   47623820910 |   2.07233344695927 |
| 2019-01-02 16:46:00.000000 |  -1251261 |   10093182491 |   2.12765957446809 |
| 2019-01-02 22:57:00.000000 |  14034018 |    9768467942 |   1.32055929570171 |
| 2019-01-03 05:24:00.000000 |  -2613264 |  -56186202376 |  -1.06769174979049 |
| 2019-01-03 16:23:00.000000 | -22956977 |   -5525892956 |  -2.04214360041623 |
| 2019-01-04 00:13:30.000000 | -10053384 |   -9177100417 |  -1.39583882012115 |
| 2019-01-04 09:05:30.000000 |  -5494943 |  -21081825567 | -0.966329662393934 |
| 2019-01-04 14:43:30.000000 |   4678636 |  -70623900257 |  -1.26799630167745 |
| 2019-01-04 20:54:00.000000 |  23879504 |   41962608843 |               0.96 |
| 2019-01-04 20:59:30.000000 |  16930573 |   41962608843 |   -0.5652942203597 |
| 2019-01-04 21:33:30.000000 |  16930573 |   49439408153 |   1.63281561131024 |
| 2019-01-05 00:16:00.000000 |  15617092 |   56326921973 |  0.552369137856527 |
| 2019-01-05 22:08:30.000000 | -28280428 | -100817061177 |  -1.41613615694426 |
| 2019-01-05 22:16:30.000000 | -28280428 |  -18081058461 | -0.364971120115236 |
| 2019-01-06 02:01:30.000000 | -11802595 |  -42569384784 | -0.958499367931412 |
+----------------------------+-----------+---------------+--------------------+
20 rows in set (0.00 sec)

This is a sample table to describe the column identity.
+----------+-------------+---------------------+---------------------+----------------------------+------------+
| columnid | fcolltype   | stime               | etime               | indinfo                    | indbpsetid |
+----------+-------------+---------------------+---------------------+----------------------------+------------+
|        1 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | VolImb                     |         15 |
|        2 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | DolImb                     |         18 |
|        3 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | PJUMP[9, high, low, close] |         50 |
+----------+-------------+---------------------+---------------------+----------------------------+------------+
3 rows in set (0.00 sec)

(The columnid corresponds to the column name of the data table.)


texts: MySQL optimization - year column grouping - using temporary table, filesort

texts:
I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date (tran_date). Even though it is using the index it creates temporary table which is taking more time because of the temporary table and the filesort. Is there any way to optimize the query to improve the query time?

SELECT COUNT( * ) AS cnt, CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS financial_year
FROM `transactions1`
WHERE tran_date >= '2010-06-01'
GROUP BY financial_year

Showing rows 0 - 4 (5 total, Query took 1.2095 sec)

id select_type  table       type  possible_keys     key key_len    ref  rows    Extra
1   SIMPLE    transactions1 range PRIMARY,tran_date tran_date 8  NULL   346485  Using where; Using index; Using temporary; Using filesort

Keyname     Type    Unique  Packed  Field       Cardinality   Collation
PRIMARY     BTREE   Yes       No    tran_date      205720         A
                                    tran_ID        617162         A
coupon_No   BTREE   No        No    coupon_No      617162         A
account_typeBTREE   No        No    account_type   3              A
prodCode    BTREE   No        No    prodCode       430            A
                                    tran_date      308581         A
tran_date   BTREE   No        No    tran_date      205720         A
cust_ID     BTREE   No        No    cust_ID        3265           A
                                    tran_date      308581         A
                                    account_type   308581         A
                                    points_earned  617162         A

Update :

Tried adding partition which is not that much helpful in comparison with non partitioned one. Does replication help in this case for reading this table?. There will be more grouping based on the dates (using the date functions) when reading the data.

Edit:

I altered the query and reduced the query execution time. The query I used is,

SELECT SUM( count )
FROM (
SELECT COUNT( * ) AS count,
CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
FROM transactions1
GROUP BY tran_date
) AS s
GROUP BY format_date

Showing rows 0 - 4 (5 total, Query took 0.5636 sec)

id  select_type     table     type  possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     &ltderived2&gt    ALL       NULL        NULL      NULL      NULL    229676  Using temporary; Using filesort
2   DERIVED     transactions1 index     NULL        tran_date   8       NULL    617162  Using index

But when using

SELECT COUNT( * ) AS count,
CASE WHEN MONTH( tran_date ) >=3
THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
FROM transactions1
GROUP BY tran_date

Showing rows 0 - 29 (229,676 total, Query took 0.0006 sec)

gives less time without using the SUM(count) in the derived table. Is there any other way to get the sum without using the subquery in MySQL or can the subquery be optimized to get the index.


texts: Keeping the same order of selected records while inserting to another table

texts:
I am running a below query in big query
insert into `test-project.temp_test_dataset.cluster_records`
(pm,col1,col2,col3,total)
select pm,col1,col2,col3,total from (
select 'ingest' as pm,col1,col2,col3,col4,col5,sum(col3+col4) as total from table1
unionall
select 'aggregated' as pm,col1,col2,col3,col4,col5,sum(col3+col4) as total from table2 order by col4
);

The select statement fetch record as:
ingest,AA,BB,CC,10
aggregated,AA,BB,CC,10
ingest,KK,LL,MM,50
aggregated,KK,LL,MM,50

But when it inserts into this table it will insert like below:
ingest,AA,BB,CC,10
ingest,KK,LL,MM,50
aggregated,KK,LL,MM,50
aggregated,AA,BB,CC,10

How to keep the same order as selected records?
Basically I would like to get the difference of column total later when rows get inserted to table cluster_records
Is there any way that I can create a some row number for each table records and can use that column with order by clause
I am running this query in shell may be something can be done using shell to generate some unique increasing number


texts: Automation of backups of Large Number of Database

texts:
In a multi-tenant web application where a database is set up for each tenant, the database can be backed up and restored manually through a fairly conventional approach.

I am considering this type of architecture for an application that could have 5,000-10,000 tenants, and running this type of process for this many tenant databases will be cumbersome.

If I was to implement such a process for a large number of tenant databases:

Is there likely to be an excessive performance overhead for such a large number of distinct backups?
How would such a large number of backup processes be automated?
How could one keep track of such a large number of backups and ensure that restore operations got the correct backup and database to restore to?
How could one implement controls to monitor the backup status and integrity of such a large number of databases?


texts: How to get past the 5000 row limit in a SQL Server Reporting Services tablix report?

texts: I have a report whose data returned exceeds 5000 rows in a tablix in SSRS 2012. When I Google this, I get all kinds of info concerning settings in Sharepoint, but this instance is running in Native mode, so Sharepoint is not involved. There is a List View Threshold setting in Sharepoint, but I can't find a reference to this in SSRS only. Also, I would prefer to increase or disable this limit per report and not for the whole instance. Is this possible? Or does SSRS simply have a hard limit of 5000 rows?


texts:
I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array columns by index and produces a new 48 array index as explained below

A = double[48] = {3,2,0,3....1}
B = double[48] = {1,0,3,2....5}

RESULT = double[48] = {A[0] + B[0], A[1] + B[1],...,A[47] + B[47]}

Thank you!


texts: How do I track the usage of XML indexes?

texts:
My XML indexes are not listed in sys.dm_db_index_usage_stats

Does this mean they aren't being used? Or does this particular DMV not track XML index usage? (Books Online doesn't appear to answer the question)

If they aren't being tracked by sys.dm_db_index_operational_stats, is there another way to know how much they are being used, or what cached plans currently use them?


texts:
I have some very large tables in my database, but a substantial chunk of this data is "old".

Due to circumstances beyond my control, I am not allowed to remove this "old" data.  The other limitation is that I cannot modify the database, meaning add filegroups to it.  The way things stand right now, everything resides on the PRIMARY file group.

I was thinking to partition these tables into a few partitions, such as "new", "old", "archived" and similar. I do have a "status" column I would like to use for this purpose.

Given the described scenario and limitations, I was wondering if partitioning makes any sense here.  In other words, if my table is partitioned in this way, but all of the partitions sit on the same filegroup, will SQL Server be smart enough to find that special area in the underlying file where my "new" data resides and not touch the area with "old" data?

To put it differently, if, let's say, 80% of my data is "old".  Does SQL Server have a mechanism to avoid accessing 100% of the underlying files and access only 20% which contains "new" data (assuming, of course, I specify my partitioning column in the WHERE clause of the queries).

I guess to answer this, one would need to understand how the partitioning is implemented internally. I appreciate any pointers.


texts: What is a Storage Definition Language (SDL)?

texts:
In my Databases lecture, the teacher mentioned that SQL is not just a data definition language (DDL), data manipulation language (DML) and view definition language (VDL), but also a storage definition language (SDL). The last usage doesn't come with any further explanations or examples.

Searching for extra information on the internet regarding using SQL as a SDL yields no relevant results. Could anyone give an example of using SQL as a storage definition language?

Edit:

Would a specification such as MySQL's SET storage_engine=MYISAM; count as a storage definition? It doesn't really relate to the data being stored, but rather, how it's stored.


texts: Is MySQL Replication Affected by a High-Latency Interconnect?

texts:
We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master.

The bandwidth between the datacenter is pretty high (in network benchmarks we've done we can reach 15MB/second), but latency exists, it is around 28ms. It's not high by any means, but it is much higher than the sub-second latency in the same datacenter.

Occasionally, we experience serious lags (2000 seconds and more) with the remove slave, while the local slave stays up to date. When looking at the lagging remote slave, the SQL thread usually spends the time waiting for the IO thread to update the relay log. The master shows "waiting for net" or something of the sort at the same time.

So it means it's network, but we still have free bandwidth at the time this happens.

My question is: can the latency between the datacenters affect the performance of replication?
Does the slave io thread just stream the events until the master stops sending them, or is it pooling the master somehow between events?


texts:
I've recently stumbled into an interesting Mysql query:

SELECT ~0;
results:
18446744073709551615

Which is the max value of a bigint unsigned. What does the tilde operator do in this case?


texts: Which SQL Server license is appropriate for my use case? Can I use a free license?

texts:
Mod note: This question is a poor fit or dba.se, but I am leaving this question because the number of people who are new to the field on an annual basis is still high, and licensing is always the hardest thing to navigate. Sometimes a helpful hand is all that we need to guide us on our journey. Sometimes we need to pay someone to teach us or review our work. Every journey will be different, and so occasionally we make exceptions. ~ jcolebrand

I have couple of databases that are used for training classes every now and then. Do I have to host these on a production server with SQL licenses or can they be treated as a non-production databases for licensing reasons?


texts:
I have a website with a chat system. This chat system has a table 'messages' and one of the columns is 'messagetext' set to utf8_general_ci(MySQL). In my website, I use php to query database. The results are ok.

In my app, i use NodeJS as backend but the results are like that: " Î±ÏƒÎ´Î±ÏƒÎ´  ". There are greek words stored in this column.

I have tried iconv-lite,iconv,utf8,decode Json.

My thought, in the website it's getting the value 'Î±ÏƒÎ´Î±ÏƒÎ' from column and decoding to utf8, that's how can I see the right results. BUT in app this is not happening, the results are the value instead of the actual word.

What can I do?

(from Comment)

CREATE TABLE messages (
    id int(11) NOT NULL AUTO_INCREMENT,
    sender int(11) NOT NULL,
    reciever int(11) NOT NULL,
    seen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
    datetime datetime NOT NULL,
    messagetext text NOT NULL,
    userseen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8


texts: "Invalid option" while creating a tablespace

texts:
Can someone tell me what is wrong with this syntax:

CREATE TABLESPACE ACME_DATA
DATAFILE '/oracle/d02/acme/ACME_DATA_01.dbf'
SIZE 40M
EXTENT MANAGEMENT LOCAL
AUTOEXTEND ON NEXT 2M
MAXSIZE 100M
UNIFORM SIZE 128KB
SEGMENT SPACE MANAGEMENT AUTO;

Error report:

SQL Error: ORA-02180: invalid option for CREATE TABLESPACE
02180. 00000 -  "invalid option for CREATE TABLESPACE"


texts: Mysql: Is it possible to handle 1000 concurrent while having low cpu?

texts:
We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5.

The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database structure is bad.

The client doesn't care about query optimization, he just wants to lower CPU usage, he runs locust, a load testing tool, give it 1500 users and 1000 concurrent, sees the CPU at in the 90s % and says this is bad. The search functionality is the problem he said.

Again I'm telling you, the queries are bad, I just got hired and doing what I can, but is he looking at the right metrics?

What have I tried to do other than fixing queries:

Adding indexes, there were no indexes, also my.cnf

innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown =1
innodb_buffer_pool_load_at_startup =1
## innodb_buffer_pool_instances should match the number of cores on the server

innodb_log_file_size = 2047M
innodb_flush_method=O_DIRECT
sync_binlog=0
innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24

## set innodb_buffer_pool to 50% to 70% of the ram on the server so if the server have 32G, then set it to 16G. 50% is a good start
## turn off slow query log, if you're not logging, and only log for small durations when you want to monitor things
slow_query_log =0

I tried to optimize what I can from the slow query log as well. Also trying to use proxySQL, which I heard it intercepts the query and sends 1/10 of the queries to the server, because of caching.

The client has a streaming app, during peak events, he hits 7k users, not sure if that counts as concurrent users and highly doubt that all of them would search. They have an on-premise server

DL380 Gen10
2x Intel Xeon Silver 4110 / 2.1 GHz
64 GB RAM
300 GB HDD

My questions:

Is there any measure one could take that I didn't know about to
reduce the CPU usage of search queries?
If I were to optimize all the queries, would I expect lower CPU under such a load test? They want the CPU usage to drop to like 50%, I just feel that they're looking at the wrong metrics. They have the right to do so because it used to be that whenever cpu got to 100%, server would go down.


texts:
In an Oracle PLSQL function I want to generate a special kind of sequence number. It starts with 00000001, but when 00899999 is reached, I want to have 01000001 as its next number. This pattern repeats when 01899999 is reached: The next number then should be 02000001.

To put it another way: My goal is to skip the numbers in the sequence in the range between ##900000 and ##999999, where ## denote two digits.

How can this be done on an Oracle database?


texts: Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

texts:
Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail log backup? if so, then why? My point of view is if my db is set into single user mode which means that no one except me accessing database, then I can omit writing NORECOVERY as the main function of this keyword is to stop the subsequent transactions.

I would like ask the same question about NO_TRUNCATE keyword. As I know, when we take t-log backup (in full recovery model) we actually truncate t-log file and by writing NO_TRUNCATE keyword, SQL Server will not truncate t-log file after backup. Why do I need to keep that inactive part when I have already taken tail-log backup successfully?


texts: multiple calls to procedure optimization

texts:
I have multiple python scripts running statistical tests and saving the results in a MySQL database.

I have chosen to create a procedure, making these scripts do lot's of calls, and only commiting in the end. (So that no info got lost, sometimes internet goes down and script gets corrupted, and other bugs)

The thing is, with this I can get multiple scripts running at the same time with some locking mechanisms, but after 3 or 4 scripts running, my server gets all 4 cores at 99/100% ...

Using htop it shows lots of mysqld processes and they all consume lots of CPU.

Anyone has any idea on how to optimize the CPU usage from mysql?

[my thoughts right now are instead of making multiple calls, create a multi-line insert, reducing the number of calls to mysql, but i'm not sure this would reduce CPU usage...]

The query

if( select exists(select id from frequency
                     where `title` like _title
                       and `subset` like _string) ) = 0 THEN
    insert into frequency(`title`, `subset`, `amount`)
                   values(_title, _string, _amount);
else
    update frequency set `amount` = amount+1
                     where `title` like _title
                       and `subset` like _string;
end if;


texts: Is B-Tree re-balanced during deletion of data from SQL Server table with a clustered index?

texts:
I have a table in a SQL Server database with a clustered index on the primary key. The table has 1 million rows. If I delete 10K rows from table, do index get restructured during deletion operation is performed ?

The deletion operation is part of stored procedure. At a time, more than one client can be executing the stored procedure, however each individual run will delete it's own set of rows (uniquely identified by primary key). I'm getting blocking on Key lock (of type U) when multiple clients executes the procedure. The blocker lock belongs to a row from the same table and it is not part of any of the concurrently running transaction. There should not be any blocking as each run is trying to delete it's own set of rows. Lock escalation is not happening as it is turned off.

I suspect, deletion operation must be causing index to re-balance and hence during restructuring process it can take key lock on any row of the table.

I would really appreciate any opinion on this.


texts:
I just got 11gR2 running and was able to conn as sysdba. I shutdown and started up the database to mount a new pfile. Now, I cannot login as sysdba. My parameter for password file is:

 *.remote_login_passwordfile='EXCLUSIVE'

I am using sqlplus within the server. This is not a remote connection.

[oracle@oel56 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 5 22:50:46 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

Here's some more information:

[oracle@oel56 ~]$ grep -E "ine SS_DBA|ine SS_OPER" $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "oracle"
#define SS_OPER_GRP "oracle"
[oracle@oel56 ~]$ id oracle
uid=500(oracle) gid=500(oracle) groups=500(oracle),54321(oinstall),54322(dba),54323(oper) context=user_u:system_r:unconfined_t


texts: Multi-Column Full Text Search Going Very Slow

texts:
I have a full text index on a table articles for the columns content, title and keywords

When doing the search on each of the columns, like so select count(1) from articles where match(content,title,keywords) against ('cats' in boolean mode), the results take between 12 and 15 seconds.

But doing the columns individually (select count(1) from articles where match(content) against ('cats' in boolean mode)) typically takes less than 50ms.

Why does searching the 3 columns take more than 100 times longer than all of them separately?

This isn't a question of how to make it faster, but instead is more asking "why is it so slow?"

Table/Indexes

id           int(30)       PK       auto_increment
url          varchar(1024)
title        varchar(255)  FULLTEXT
content      text          FULLTEXT
keywords     varchar(1024) FULLTEXT
comments     text
created_date int(11)
posted_date  int(11)

Explains

This first one is the multi-column query:

This second is the new much faster query that runs the 3 columns separately then unions them (query cache was cleared).

Full Text Column Order

Use/Force Index with Explain


texts: Is there an analog of GETDATE() that returns DATETIME2

texts:
According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that:

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(CURRENT_TIMESTAMP) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t
FROM #t
ORDER BY t ;

---

2013-01-28 13:23:19.4930000
2013-01-28 13:23:19.4970000
2013-01-28 13:23:19.5000000
2013-01-28 13:23:19.5030000
2013-01-28 13:23:19.5070000
2013-01-28 13:23:19.5100000
2013-01-28 13:23:19.5130000

(snip)

Is there a similar function that returns DATETIME2(7)?


texts: Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

texts:
I just started dabbling with the query cache for our CMS.

Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?

query_cache_size=512M
query_cache_limit=1M

This is how it looks after about 12 hours

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 10338     |
| Qcache_free_memory      | 297348320 |
| Qcache_hits             | 10254104  |
| Qcache_inserts          | 6072945   |
| Qcache_lowmem_prunes    | 725279    |
| Qcache_not_cached       | 2237603   |
| Qcache_queries_in_cache | 48119     |
| Qcache_total_blocks     | 111346    |
+-------------------------+-----------+

This is how it looked after flush query cache;

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 443559256 |
| Qcache_hits             | 10307015  |
| Qcache_inserts          | 6115890   |
| Qcache_lowmem_prunes    | 725279    |
| Qcache_not_cached       | 2249405   |
| Qcache_queries_in_cache | 26455     |
| Qcache_total_blocks     | 54490     |
+-------------------------+-----------+


texts: Create menu from multiple meals relationship

texts:
I have the following relations:

Meals(meal_id, name, cuisine, ingredient)

Users(user_id, name, username, password, email)

Chefs(chef_id, name, username, password, email)

I want to create the following relation and schema: a relation that create menus from multiple meals for different users

Also, user can use menus that created by chefs.

I tried the menus(menu_id, name) and put menu_id attribute into Meals. it didn't work


texts: Group by with Order by DESC not working using PHP MySQL codes

texts:
I have three tables,
table one name (tbl_brands) with these columns:

and second table name (tbl_loader_attachment) with these columns:

and the third table name is (tbl_images) with these columns:

I used this query below to sort brands DESC:

SELECT tbl_loader_attachment.*, tbl_brands.* ,tbl_images.* FROM tbl_loader_attachment
INNER JOIN tbl_brands ON(tbl_brands.b_id=tbl_loader_attachment.b_id)
INNER JOIN tbl_images ON(tbl_images.la_id=tbl_loader_attachment.la_id)
WHERE tbl_loader_attachment.type_brand_id=1
GROUP BY tbl_images.b_id ORDER BY tbl_loader_attachment.la_id DESC

When I run this code only get the first row appear I want to get only the newest brand appear when I inserted,  in my tables I want to in the tbl_loader_attachment get row 4, and in the tbl_images get row 8 at the same time but when I used group by only the first row appear.

this is my Output:

How i can fix?


texts: Is there a way to see completed process list in mysql (SHOW FULL PROCESSLIST)

texts:
I am new to Mysql server, I use the below query to see what are all processes or queries that users are currently executing.
SHOW FULL PROCESSLIST;

I needed to check those processes, which users executed in past. like those processes which were executed and they got completed or aborted.
Is there a way to check the already executed processes via any query in MysqL server ?


texts: SQL Server 2016 SP1 Replication: creation of publication stops with nvarchar(max) not supported with memory optimized tables

texts:
We're trying the following setup:

SQL Server replication on SQL Server 2016 SP1.
We're using Transactional replication on this server to push transactions to the subscriber.
We can select the table we're trying to replicate. We check the checkbox telling the publication that it needs to be memory optimized. The table contains one column of the NVARCHAR(MAX) type, which is supported in SQL 2016.

So far so good.

But when we create the publication, the following error occurs:

The type 'nvarchar(max)' is not supported with memory optimized tables.
Changed database context to 'StackExchange'. (.Net SqlClient Data Provider).

Any thoughts on a solution? My fallback will be transaction log shipping.

Found some links that indicate the nvarchar(max) datatype should be replicationable:
https://technet.microsoft.com/en-us/library/ms151254%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/table-and-row-size-in-memory-optimized-tables

Found another resource telling me that the @schema_option should be set to 0x40000000000. Still the same error...

use [StackExchange]
exec sp_addarticle @publication = N'T04'
    , @article = N'Badges'
    , @source_owner = N'3DPRINTING'
    , @source_object = N'Badges'
    , @type = N'logbased'
    , @description = N''
    , @creation_script = null
    , @pre_creation_cmd = N'drop'
    , @schema_option = 0x40000000000
    , @identityrangemanagementoption = N'manual'
    , @destination_table = N'Badges'
    , @destination_owner = N'3DPRINTING'
    , @vertical_partition = N'false'
    , @ins_cmd = N'CALL sp_MSins_3DPRINTINGBadges'
    , @del_cmd = N'CALL sp_MSdel_3DPRINTINGBadges'
    , @upd_cmd = N'SCALL sp_MSupd_3DPRINTINGBadges'
GO

I've created a Connect item with Microsoft for this issue.
https://connect.microsoft.com/SQLServer/feedback/details/3133643/replication-to-memory-optimized-table-fails


texts: Obtaining database information and values in extended properties

texts:
I need to obtain each DB (together with its information) from SQL Server and I am doing so by the below:

use master;
select *
from sys.databases
where database_id > 4
order by name

I also need to obtain a particular value from extended properties. Currently I am doing the below FOR EACH DB manually to obtain this value:

use <db_name>
select value from sys.extended_properties
where class_desc='DATABASE' and name = 'Application Name'

However, I need to get the value outputted from the last query in a column for each of the DB displayed by the first query respectively. Can this be done?


texts: Index and rows placement within database files

texts:
Where are the indexes and data tables stored within a physical database file or a page of a physical file :

Can there be different rows from different tables in the same
physical page ?
How does MySQL decide where to place a row in a
certain page?
What about indexes placement too ?

I know a little about that for Network Database (IDMSX database), with direct calculated placement on a physical page (and other records stored just near if possible).


texts:
My schema 'test' has 3 tables: 'user', 'post' and 'revision'.

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`iduser`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`post` (
  `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `iduser` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
  `revision_count` INT NOT NULL DEFAULT 0,
   PRIMARY KEY (`idpost`),
   INDEX `fk_post_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_post_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `fk_revision_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_revision_post`
     FOREIGN KEY (`idpost`)
     REFERENCES `test`.`post` (`idpost`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT `fk_revision_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

And there is the script creates data:

INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann');
INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');

INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');

I create 2 sessions work concurrently. Session 1 works with query:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES
  ((SELECT max_idrevision + 1
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1),
   1, 2, 'abc xyz');

Now session 2 works:

BEGIN;
UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;
-- will hang by lock

-- but with this update
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;
-- not hang

I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ? If it's row lock, why does second update query in session 2 not hang ?


texts:
I have a bunch (millions) of flat files. I do sort them into subdirectories, but that doesn't really help searches.

Is there some database software that could be fed metadata information and would provide a find-like interface on top of the metadata?

If the database could handle the flat files as well, then even better.

I know that I could just put the metadata into an SQL db and write some queries on top of it, but that would be the last resort.

Alternatively, is there a db that could handle files that consist of a variable number key = value records of variable lengths.


texts: local login from DBA group member (as sysdba) not working

texts:
good day!

i'm confronted with the following situation

OS: CentOS 7 x86_64
user: oracle (member of DBA group, owner of /u01/app (drwxr-sr-x)
/etc/oratab: orcl12c:/u01/app/oracle/product/12.1.0/dbhome_1:Y
after starting LISTENER manually, Listener reports "The listener supports no services"
sqlplus sys/*@co7ora12cvm:1521/orcl12c as sysdba returns
«ORA-12514: TNS:listener does not currently know of service requested in connect descriptor»
dbstart starts listener but errors out with ORA-01017: invalid username/password; logon denied
i'm able to start the database through dbca (Configure database options > Next > Next)
once this is done LISTENER know of the services orcl12c and orcl12cXDB
once this is done, sqlplus username@orcl12c works fine
sqlplus as sysdba throws the /? output.
ping co7ora12cvm works
tnsping orcl12c times out (TNS-12535)

listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = co7ora12cvm)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

shrept.lst

(##) function entry points for genclntsh.sh

network : snaumihi_inithostinfo
network : snaumbg_gmt
network : naedpwd_encrypt
network : naumbsb_bld_singlebyte
network : nlgh
network : nsdhctx_establish
network : nsdhctx_respond
network : nsdhctx_close
network : nsdhctx_inuse
network : snlsmDscFromString
network : snlsmCreate
network : snlsmDestroy
network : snlsmDscToString

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NONE)

tnsnames.ora

ORCL12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = co7ora12cvm)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl12c)
    )
  )

this is a new install that has never started "normally"

thanks in advance for any suggestion

############# UPDATE user/hosts

as already mentioned i do this as user oracle which is member of the dba team

this is run on vmware and i do the networking with the hosts files

/etc/hosts on co7ora12vm

127.0.0.1       localhost.localdomain localhost
192.168.178.30<>co7ora12cvm.localdomain co7ora12cvm

c:\windows\System32\drivers\etc\hosts (on Windows 7)

192.168.178.30          co7ora12cvm.localdomain co7ora12cvm

############# UPDATE sqlnet.ora

thanks for spotting this, indeed, alas it makes no difference

sqlplus sys/sys@co7ora12cvm:1521/orcl12c as sysdba gives ORA-12514: TNS:listener does not currently know of service requested in connect descriptor  before  instance is started through dbca; once instance is started it connects
sqlplus / as sysdba  always gives ORA-12162: TNS:net service name is incorrectly specified

this happens no matter what sqlnet.ora contains

SQLNET.AUTHENTICATION_SERVICES = ()
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) such as on my 10g vm where sqplus / as sysdba works

?


texts: Checking when a row was inserted into a table on SQL Server?

texts:
Is it possible to check when a specific row was inserted into a specific table in SQL Server ?
The table has an index if this any help.

And I need to do this for an existing table where the row has already being inserted.

Thanks.


texts: Error 1290 despite of fixing my.ini file and having FILE permissions

texts:
I tried loading data from a file but got ERROR 1290.

mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.01 sec)

mysql> load data infile "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Book1.csv"
into table sample fields terminated by ',';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
so it cannot execute this statement

I've aready tried the below link but didn't help.

    https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option

I also have FILE privilege for the current_user.

That's how Book1.csv looks

1,A,A1
2,B,A2
3,C,A3
4,A,A4
5,B,A5
6,C,A6
7,A,A7
8,B,A8
9,C,A9
10,A,A10

I've spent hours. Pls help me out.


texts:
I am just configuring a Galera 10.1.14 cluster and i want to set the Async replica from two different clusters.

Cluster 1 -> Data Schema A \
                            ==(WAN)==> MariaDB 10.1.14 DRS Async Node
Cluster 2 -> Data Schema B /

But AFAIK on the DRS Async Node can be configured only ONE master at a time so, every cluster as to be syncronized to a DRS node.
If there any way to sync to clusters to the same DRS node??

Thanks!


texts: Learning to draw entity-relationship diagram for a scenario involving Offices, Employees and Projects

texts:
I am trying to learn entity-relationship diagram, following question is the objective.

  Information consulting firm has number of offices with employees. The employees can move offices depend on the project, but location is always recorded. This consulting firm manage number of projects and have vast base of clients. The company's success is depend on assigning relative employee with relative skill and location. The time an employee spends on project is recorded each day. This include time for travel and break time etc. To reduce
  time for travel they try to allocate employees from the nearest office possible.

  Draw Entity Relationship diagram and entity listing with attributes.

I created entity-relationship diagram by using Visio. Please comment on my attempt.

Office

Address
telephone
office manager

Employees

name
address
job
hourly rate
start_date
location etc

Projects

Name
Manager
Start Date
End Date
Estimated Cost
Quote
Actual Cost

Client
Name
address
telephone
e-mail
contact

Assignment

name
Employee name
Date Assigned
Hours spent

Here is my attempt in this image:


texts: SQL select with unique_list - losing an item

texts:
I have an oracle database and I have the following problem:
If I make an select like this:

SELECT fk_proddes_id,fk_id_cod_deseu,
     LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare)
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

I get the following result:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 11,D 7
301             506             D 1,D 15

I need the unique code, so if I put:

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 7
301             506             D 15

so, I "lose" the D1 - don't know why...

If I put

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare DESC))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 7,D 11
301             506             D 15,D 1

But I need D1,D15 and D11,D7. What's wrong with the second query?  Why do I "lose" D1 ???

Thanks!


texts:
I have configured log shipping in my primary serverA and secondary serverB.

Everything works fine until I bring the standby database online.

Users are not able to login to the secondary serverB database (which is on the secondary server, which is brought online)

Can anyone help me on this?


texts: SQL Server 2008 - Return records in chronological order, given a separation factor

texts:
This question was originally posted here https://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice.

I have a massive table in SQL Server 2008, it contains the position reported by technicians every minute. I need to report on this table but in order to control the amount of records that are displayed in the report both a time and distance separation factors need to be taken into account.

So, a query may look like : "Return all records with no less than 5 minutes and/or 300 feet between them".

The time part is done, but I'm having a hard time with the distance factor. I have the latitude and longitude for each point, and I have no problem if I need to include a SQL Server 2008 spatial UDT in order to resolve the problem.

Things I have considered:

Bring the records by the time factor, and apply the separation constrain in the client by calculating the distance between adjacent points and discard those which falls inside the the factor. (the easiest, but it must be the one consuming more resources).
Keep the last record per technician in a cache, pre-calculate the distance between the record and its predecessor, and resolve the constrain in the client. (should consume less resources than 1) since the distance is pre-calculated, however and since the table is BIG It will increase the size of the dataset, not sure if the space is worth the processing savings).
Use the spatial functions in SQL Server 2008, but honestly I had been reading and I couldn't find anything that helps me resolve this type of requirement. Any GIS expert??

I would like to go with the best option possible (maybe not listed above?) and IMO should be the one using the SQL Server features most efficiently.


texts:
Today I though I'd give Firebird 3.0 a shot, but for some reason I can't seem to get it installed - at least not in a way that I can connect from a JDBC application.

What I did was to follow this article:

http://firebird3.blogspot.com.br/2012/03/how-to-install-firebird-3.html

This is what happened:

c:\etc\Firebird30>gsec -add sysdba -pass masterkey

c:\etc\Firebird30>start firebird.exe -a -m

c:\etc\Firebird30>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "localhost:c:\temp\mydbfb3.fdb" user "sysdba" password "masterkey";
Statement failed, SQLSTATE = 08006
Error reading data from the connection.
SQL>

Now creating the database "locally" works:

SQL> create database "c:\temp\mydbfb3.fdb" user "sysdba" password "masterkey";
SQL> commit;
SQL>

But in that case, when I connect from a JDBC application (still everything "localhost"), I get the error message (using sysdba/masterkey to log in)

GDS Exception. 335544472. Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
Reason: Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

I double-checked (more than once) that the username and password in the JDBC application is sysdba/masterkey.

From the commandline I can indeed access that newly created database:

SQL> connect 'c:\temp\mydbfb3.fdb';
Database:  'c:\temp\mydbfb3.fdb'
SQL> create table foo  (bar integer);
SQL> insert into foo values (1);
SQL> select * from foo;

         BAR
============
           1

SQL> commit;
SQL>

I'm sure I'm missing something obvious, but what?


texts: Populating an empty table using loops, conditionals, and accessing multiple tables

texts:
This question pertains to a MySQL database, although, in the end, this may be database neutral.

I am trying to populate an empty table (let's call it NewTable) that I created. The data population is based upon retrieving a bunch of data from other tables, along with occasional checking of that data to create a given value. The crux is that I want to get this data for every person found in a given table, let's call it table A. And there are conditionals involved too as I retrieve some of this data. Here is some pseudocode for it….

For each personID in TableA
    select statistic from a column in TableA for that personID as column 1 of NewTable
    select statistic from a column in TableB for that personID as column 2 of NewTable
    select statistic from a column in TableC based upon a value for that personID found in another TableD
          If selected statistic has value X, then assign column 3 of NewTable a certain value; otherwise assign column 3 of NewTable another value
    :
    :
End Loop after data retrieved for last personID in TableA

NewTable should now be populated after this loop ends.

Here is a bit more specific information based on the comment below:

**TableA:**

PersonID : char(6)
Points : Decimal(3,1)

**TableB:**

PersonID : char(6)
Points : Decimal(3,1)

**TableC:**

UID : tinyint(6)
Seas : int(10000)
Wk : tinyint(20)
V : char(3)
H : char(3)

**TableD:**

PersonID : char(6)
Grp : char(3)

**NewTable:**

PersonID: char(6)
Points : Decimal(3,1)
Points_Alt : Decimal(3,1)
Pattern : tinyint(6)

For each PersonID AS current in TableA:
   SET current AS PersonID IN NewTable
   SELECT Points FROM TableA WHERE PersonID = current AS Points IN NewTable
   SELECT Points FROM TableB WHERE PersonID = current AS Points_Alt IN NewTable
   For each UID in TableC WHERE Seas = 2011 AND Wk = 10:
        if V == SELECT Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 3 AS Pattern IN NewTable
        else if H = select Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 6 AS Pattern IN NewTable
    End Loop
End Loop

Can anyone provide me an example of how to do this using a SQL query? Some actual example SQL code would be very helpful.

Thank you.


texts: Where am I going wrong while trying to install sample database in MySQL?

texts:
I wanted to load the Sample Database Menagerie into MySQL. So, it's showing 'FAILED TO OPEN' in MySQL.
My text.file for the same is
Drop database if exists menagerie ;
CREATE DATABASE menagerie ;
USE menagerie ;
SOURCE C:/SampleDB/cr_pet_tbl.sql
LOAD DATA LOCAL INFILE 'C:/SampleDB/pet.txt' INTO TABLE pet ;
SOURCE C:/SampleDB/ins_puff_rec.sql
SOURCE C:/SampleDB/cr_event_tbl.sql
LOAD DATA LOCAL INFILE 'C:/SampleDB/event.txt' INTO TABLE event ;

And these are the details of Menagerie DB

I have also Uncompressed the file.

But still there's an error!

Where am I going wrong please can anyone tell? Don't ignore please, it takes a lot of efforts to type so much here.
I have downloaded Menagerie from MySQL site into a folder SampleDB on drive C.


texts: Activity Monitor Blocked by Firewall. Set Static Port?

texts:
We are seeing an issue with not being able to open Activity monitor for remote SQL servers.

The Activity Monitor is unable to execute queries against server .
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (mscorlib)

It appears some firewall is blocking it,
When we look at firewall logs (on the network), we are seeing blocks from 496xx. And its different port for each server.

Is anyone aware if we can make this a static port?


texts: I want to import tables from one sql server to another server using SSIS with loop

texts:
I want to import table via SSIS, This package will execute like first it'll import first 10 tables once its imported then it'll start to import 10-20 tables, like a loop.

Can anyone explain me how can I achieve this?


texts:
I have 4 million rows in MySQL. I have a count query to fetch from DB to Apache web server.

The issue is first load (Count query) - when I click fetch button, it takes more than 5 mins.
Second time when I click fetch button in web app i takes less than 5 seconds.

I have added indexing and performance tuning(Removed WHERE, JOIN and many other tuning) has been implemented. Also Cache is also implemented.

Still it takes more time for first time.


texts: COPY TO .csv and add quotes to header in PostgreSQL

texts:
I use the following in the command line

\copy (select id, pid from table1) to '/path/to/file.csv' with delimiter ';' CSV HEADER;

Everything works fine. But I want the header's string to be quoted in '"'.
Does anyone know if that's possible?
My PG-Version is 9.3.

Thank you very much!


texts: postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger

texts:
I would like to use a custom text search configuration with tsvector_update_trigger to be able to update tsv field with french dictionary and unaccent.

So I did that :

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

and then created trigger :

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(tsv, 'pg_catalog.fr', title, authors, descriptionText);

but unfortunately when I want to create or update a row I get an error telling me that fr does not exist.

How I can use this configuration with a trigger ?


texts: Disk space full during insert, what happens?

texts:
Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself.

The facts:

Usual database size is about 55 GB, it grew to 605 GB.
Log file has normal size, datafile is huge.
Datafile has 85% available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated).
Tempdb size is normal.

I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened:

SELECT INTO is executed
Target table is created
Data is inserted as it is selected
Disk fills up, causing the insert to fail
SELECT INTO is aborted and rolled back
Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space.

In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this:

BEGIN TRANSACTION
SELECT  T.x
INTO    TMP.test
FROM    (VALUES(1))T(x)

ROLLBACK

SELECT  *
FROM    TMP.test

This results in:

(1 row affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'TMP.test'.

Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table?

Are the assumptions I sketched here correct? Is this a likely scenario to have happened?


texts: Write latecy increased when changed SQL Server to Enterprise on AWS

texts:
We have been using AWS - Rds SQL Server 2016 Standard Edition with db.m4.4xlarge db instance (16 CPU, 64GB mem). Today, we needed to upgrade db instance to a large capacity machine, (db.r3.8xlarge - 32 CPU, 244GB mem).

To do so:

we had to take snapshot of the db instance
restored the snapshot as a new db instance with that large instance.

We have done everything perfectly. After we upgrade the instance, we realised that sql queries are really really slow. I know its because the buffer cache and plan cache cleared bcs of this upgrade, but this is really a bad situation for a production db. Every single query, when first executed, reads from disk, not from buffer. We have thousands of queries coming from tens of applications, so should I need to wait for our latency goes down?


texts: How to get usage based optimization working on SSAS?

texts:
I have set up the configuration for SSAS to log the usage into the database, since the table did not exist I let it do that. However SSAS created the table OlapQueryLog under the schema of the SSAS service account. The logging of the queries against SSAS works fine, however when using the usage based optimization wizard it fails, because it expects the table OlapQueryLog to be under the [dbo] schema. I'm puzzled that one part of SSAS is able to write to the table and another part can't find the table, one would assume they both use the same meta information to figure out where to look for their required table.

Where and how can I tell the wizard to use the table that SSAS created ?


texts:
I got the error below:
ERROR: canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.;
nested exception in org.postgresql.util.PSQLException:ERROR:canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.

This error occurs on the standby, when we use long query.
We use PostgreSQL version 12.0. One master and two standby servers.
Could you please give us the solution?


texts: Generate incrementing IDs without sequences

texts:
Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
CREATE TABLE documents (
  id SERIAL PRIMARY KEY
, document_id INT NOT NULL
, user_id INT NOT NULL
, text TEXT);

For each user document_id starts with 1 and increases preferably with gaps being a rare occurrence. The obvious solution is to create a sequence for each user get the document_id from there. But according to this databases don't behave well when there are lots of relations there. Another solution is to store next_document_id in users table and update it as necessary, but that means the lock on this row will be highly contested slowing simultaneous transactions from the same user. Any other ideas?


texts:
I've got a table with an image blob field in it, which stores jpg files.

As those jpg files are shown in a web page, I would like to resize them in order to spend less bandwidth when my web page call them.

First, I thought I could resize a jpg file, stored in blob field, when doing a select statement and using command ORDimage.process. But it seems it's not possible.

CREATE OR REPLACE FUNCTION ORGPOLV2.resize_img (i_cod_op INTEGER)
      RETURN blob
   IS
      v_photo     blob ;
   BEGIN

     select img_simbolo_op
      into v_photo
      from tbl_op
      where cod_op=i_cod_op for update;

     ordimage.process(v_photo, 'maxScale=32 32');

 update tbl_op set img_simbolo_op = v_photo where cod_op=0;
--
commit;

    select img_simbolo_op
    into v_photo
    from tbl_op
    where cod_op=0;

      RETURN v_photo;
   END resize_img;
/

With this function, I got error:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "RESIZE_IMG", line 10

My question is: Is it possible to resize a jpg file in a blob field?

Maybe my approach with ordimage.process is wrong and there is another way.


texts: Keep the foreign key constraint using soft delete

texts:
I have a product table and it is foreign key for product ingredients table.
Is there a way to check if it is fk referenced before making isdeleted = true without manually querying for it?


texts: PostgreSQL: how do I query over a column with a custom range?

texts:
I'm trying to write a query over a string column called grades_served that can have values such as

k-5
1-5
5-9

The query could be for a specific grade or it could be a range with a start and end value.

I wanted to check what the best approach would be on this. My gut instinct tells me to reformat the column into a range datatype, but the issue is this is a complex range, with string values, such as k and Pre-K.

I thought about creating a custom type but that seems like an overkill and I've never done that before.

http://www.postgresql.org/docs/9.3/static/sql-createtype.html

Would really appreciate any thoughts or recommendations.


texts:
My site having a more than 1.5 million records and my question is the how may i fetch record fast.

i am spending so much time to find the way for it finally find out the one way which is to apply the indexing on the field. also try to apply on that it's better result than before.

but still it taking to much time to getting data from the database can any one tell me other technique or trick which i applied on that so it give me much better performance. and my site is built in laravel 4.2.

You can see also my query like below

     $result = DB::table('bussiness')
                ->select(array('bussiness.*', DB::raw('COUNT(complaintreport.id) as reported')))
                ->where('bussiness.is_approved', 1)
                ->where('bussiness.status', 1)
                ->where(function($sql) use ($data) {
                    $sql->where('bussiness.bussiness_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                    $sql->orWhere('bussiness.trading_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                })
                ->leftjoin('complaintreport', 'complaintreport.bussiness_id', '=', 'bussiness.id')
                ->groupby('bussiness.id')
                ->get();

Thanks in advance.


texts:
can anyone give me some advice on the following situation:

I have two basic tables:

Then a view created by the two:

select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN
    from PRODUCT
    inner join COMMANDPRODUCT on commandproduct.idproduct = product.idproduct
    inner join PRODUCTSTORAGE on PRODUCT.IDPRODUCT = PRODUCTSTORAGE.IDPRODUCT
    group by product.idproduct, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN order by datainput asc;

How can you obsserva i have 4 records with idProduct = 28, and totalOut for all together is 18, and not individually.
To me that the result should look like the following:

I would like the result to look like this:

How could I do that?


texts: Is making joins between different table spaces slower?

texts: In MySQL and PostgreSQL, is making joins between different tablespaces inherently slower, compared to joining tables in the same tablespace?


texts: Seperate tables (current situation) or optional columns

texts:
I'm working on a little project that relies on an SQLite database to generate data. The setup is like this:

There is a list of categories with possible subcategories.
Each category can have map data.
Each category can have achievements.
Each achievement can have more specific things like a right click menu.

My question here relates to the achievement part of the database. As you can see in the image below, the Achievement table has a mandatory ID and Faction. Optionally it has a flag to disable the Wowhead Link, a flag to indicate if it's  Not Obtainable and a link to a Covenant (this is also a 1-to-1 relation but initially thought it was 1-to-many). These last 3 are in separate tables.
Getting everything has resulted in a pretty big query.
SELECT
    A.ID, ACA.Location, ANO.ID, AHNWL.ID, A.Faction, AC.CovenantID
FROM
    Achievement A
    LEFT JOIN AchievementNotObtainable ANO
        ON A.ID = ANO.ID
    LEFT JOIN AchievementHasNoWowheadLink AHNWL
        ON A.ID = AHNWL.ID
    LEFT JOIN AchievementCategoryAchievement ACA
        ON A.ID = ACA.AchievementID
    LEFT JOIN AchievementCovenant AC
        ON A.ID = AC.AchievementID
WHERE
    ACA.CategoryID = @Category
ORDER BY
    ACA.Location ASC;"

So, the reason why I did this database design is to reduce the amount of data. But is this actually true? Does this multiple table design use less data compared to when I would just have 3 extra columns in my Achievement table with empty records? I don't see this table to have more than 25k records.


texts:
I was testing my DB with Mongo 3.0 wiredTiger setup.
After restoring my database with wiredTIger i ran db.serverStatus.
I found following in console output.

"bytes currently in the cache" : 6009288998 (5.5 GB),
"maximum bytes configured" : 7516192768 (7 GB),
"bytes read into cache" : 18380155206 (17 GB),
"bytes written from cache" : 17020204785 (16 GB),

Could you please tell em what this output means.

I have 16 GB RAM in my server.
Database configuration is as follows:

MongoD instance 1
Databases 4
Original Data size with 2.6.4 : DB1- 25 GB, DB2- 19 GB, DB3- 5 GB, DB4- 3 GB
With wiredTiger : DB1- 3 GB, DB2- 1.9 GB, DB3- 0.9 GB, DB4- 0.5 GB

I was looking for updatye performance but it seems that performance seems to be degraded and when run bulk update on DB3 with 23000 update command
I saw this in Mongostat

    insert query update delete getmore command % dirty % used flushes vsize  res qr|qw ar|aw netIn netOut conn     time
        *0    *0     *0     *0       0     2|0     0.6   80.0       0  7.7G 7.4G   0|0   1|1  137b    15k    6 11:21:27
        *0    *0     *0     *0       0     3|0     0.9   79.9       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:28
        *0    *0     *0     *0       0     1|0     1.2   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:29
        *0    *0     *0     *0       0     1|0     1.4   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:30
        *0    *0     *0     *0       0     2|0     1.7   79.9       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:31
        *0    *0     *0     *0       0     1|0     2.0   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:32
        *0    *0     *0     *0       0     3|0     2.2   80.0       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:33
        *0    *0     *0     *0       0     1|0     2.4   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:34
        *0    *0     *0     *0       0     1|0     2.6   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:35
        *0    *0     *0     *0       0     2|0     2.9   80.0       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:36
    insert query update delete getmore command % dirty % used flushes vsize  res qr|qw ar|aw netIn netOut conn     time
        *0    *0     *0     *0       0     1|0     3.0   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:37
        *0    *0     *0     *0       0     3|0     3.1   80.0       0  7.7G 7.4G   0|0   1|1  195b    15k    6 11:21:38
        *0    *0     *0     *0       0     1|0     3.3   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:39
        *0    *0     *0     *0       0     1|0     3.5   80.0       0  7.7G 7.4G   0|0   1|1   79b    15k    6 11:21:40
        *0    *0     *0     *0       0     2|0     3.7   80.0       0  7.7G 7.4G   0|0   1|1  133b    15k    6 11:21:41

If you see dirty% is getting increased continuously.
Also after completing the updates i left the Db without untouched (no read and write) But still i was getting netIn and netOut showing bytes.

Could you please check the behaviour and explain it more as Mongo DB document section is not showing any thing in detail or these parameters.

Also i am not sure with one point does compression of index also reduces RAM requirement.


texts: Speed up creation of Postgres partial index

texts:
I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.

My data is completely static, so I am able to insert all data, then create all indexes.

In this 1.2TB table, I have a column named run_id that cleanly divides the data. We've gotten great performance by creating indexes that cover a range of run_ids. Here's an example:

CREATE INDEX perception_run_frame_idx_run_266_thru_270
ON run.perception
(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

These partial indexes give us the desired query speed. Unfortunately, the creation of each partial index takes about 70 minutes.

It looks like we are CPU limited (top is showing 100% for the process).
Is there anything I can do to speed up the creation of our partial indexes?

System specs:

18 core Xeon
192GB RAM
12 SSDs in RAID
Autovacuums are turned OFF
maintenance_work_mem: 64GB (Too high?)

Table specs:

Size: 1.26 TB
Number of rows: 10.537 Billion
Typical index size: 3.2GB (there is a ~.5GB variance)

Table definition:

CREATE TABLE run.perception(
id bigint NOT NULL,
run_id bigint NOT NULL,
frame bigint NOT NULL,
by character varying(45) NOT NULL,
by_anyone bigint NOT NULL,
by_me bigint NOT NULL,
by_s_id integer,
owning_p_id bigint NOT NULL,
obj_type_set bigint,
seq integer,
subj_id bigint NOT NULL,
subj_state_frame bigint NOT NULL,
CONSTRAINT perception_pkey PRIMARY KEY (id))

(Don't read too much into the column names -- I've obfuscated them somewhat.)

Background info:

We have a separate team onsite that consumes this data, but really there are only one or two users. (This data is all generated via a simulation.) Users only start analyzing the data once inserts are finished and indexes are completely built. Our main concern is reducing the time required to generate usable data, and right now the bottleneck is index creation time.
Query speed has been completely adequate when using partials. In fact, I think we could increase the number of runs that each index covers, and still maintain good-enough query performance.
My guess is that we will have to partition the table. We are trying to exhaust all other options before taking that route.