texts: optimizing MySQL for traffic analytics system

texts:
background :

I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data.
currently I'm using MySQL and have a table to store click info with this schema:

visit_id (int)
ip (int)
date (datetime)
country
browser
device
os
referrer (varchar)
url_id (int)  //as foreign key to the shortened URL

and for now , just the url_id field has index

The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ...

for example to generate graphs for past month , I do following queries:

SELECT all DAY(date) AS period, COUNT( * )
                        FROM (

                        SELECT *
                        FROM visits
                        WHERE url_id =  '$url_id'
                        ) AS URL
                        WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH )
                        GROUP BY DAY( DATE )

//another query to display clicker browsers in this period
//another query to display clicker countries in this period
// ...

issues:

for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible.
lots of memory and CPU is needed to run such queries

questions :

1- how to improve and optimize the structure , so the analytics of high
   traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage
   of CPU and RAM ? should I make an index on the fields date,
   country, browser, device, os, referrer ? if yes , how to do
   that for the field date because I should group clicks some times by
   DAY(date), sometimes by HOUR(date), sometimes by MINUTE(date)
   and ...

2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example?

3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions?

UPDATE: I made an index on column referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server.

I think making index on these columns would not help to solve my problem, my idea is about one of these:

1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request.

2- using some caching solution like memcached to help MySQL with high traffic links.

3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever.

what do you think?


texts: Restrict users from modifying datasources in SSRS

texts:
We had a situation lately when a particular user of the SSRS reports modified the datasources defined on the reporting server. These users have access rights to create ad-hoc reports using reportbuilder. Because of this it's turning out to be tricky to manage the access using pre-defined roles.

Is there a way to restrict users form having access to modify the datasource, yet have the ability to use datasources for ad-hoc reports?


texts:
I'm a beginner and student with regards to database design. The tutor recently discussed “part-keys”. I am struggling to find any information on what a part-key is, and I suspect it probably goes by another name.

The example we were given in class was this:

ACTIVITY(Activity(PK), Fee)
STUDENT_ACTIVITY(StudentID(PK), Activity(PK))

  Is the table showing part-key dependency?

  Yes. Since the second determinant (Activity) is part of the primary key (StudentID, Activity).

Is anyone able to give me any further information?


texts: How to determine the biggest float value (in terms of used bytes) with SQL in Oracle?

texts:
I would like to know how to check out what is the biggest float value in terms of used bytes in a specific column.

Oracle DB Version is 10.2.


texts: Oracle Database 12c: Unable to resize system tablespace

texts:
Problem:

My java application is returning ORA-01691: unable to extend lob segment errors for every insert

Cause:

After running:

SELECT *
FROM dba_tablespace_usage_metrics
ORDER BY used_percent desc;

I found that the SYSTEM datafile is 99.81% full (and is only 4MB for some reason).

Solution Attempts:

I turned autoextend on with

ALTER DATABASE
DATAFILE '/home/user/oracle/orcl/system01.dbf'
AUTOEXTEND ON NEXT 2048M
MAXSIZE UNLIMITED;

...which executed fine, but did not increase the size.
I then tried to manually increase the size with:

ALTER DATABASE
DATAFILE '/home/user/oracle/orcl/system01.dbf'
RESIZE 2048M;

...which gave an error that the new size I was trying to use would end up truncating data. That makes no sense of course since the current size is 4MB. I also tried pasting in 2GB as bytes instead of "2048M" because I thought it may not understand "2048M". That gave the same error though.

What is the actual, working way to get rid of the ORA-01691: unable to extend lob segment error?


texts: What do you call numerical coding systems that are left-aligned?

texts:
I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about best-practices and navigation of this data, and I'd like to simply refer to it by the right name.

Essentially this is an example of the data

CODE, TITLE
"21","Mining, Quarrying, and Oil and Gas Extraction"
"212","Mining (except Oil and Gas)"
"2121","Coal Mining"
"21211","Coal Mining"
"212111","Bituminous Coal and Lignite Surface Mining "
"212112","Bituminous Coal Underground Mining "

So if Bituminous Coal Underground Mining was your organization type, your code would be 212112. You could look up to find you were in the business-genre of Coal Mining, and up again to find you were in the business of Mining (except Oil and Gas), and up again to find you were in the business of "Mining, Quarrying, and Oil and Gas Extraction".

What is such a scheme called, is there a term to refer to this kind of organization of data?

I want to call something like recursive-base10 or recursive-decimal is there a name of it though?


texts:
I'm working on a project using a MySQL database and we'd like to combine a query which was previously made in several steps into one step.
The queries are created in a Java-App using Hibernate.

Goal
The goal of the query (or queries up to now) is the following: Loading a subset of data from several different tables (according to some filters and sorted).

Database Structure
The structure of the database can not be changed. I'll show here the needed tables with some example entries.

Table t1

id         class    counts      ... other parameters
-------    -----    ------
1          B5.2     124831
2          A7.9     83482
3          M5.5     53124812

Table t2

event_id    reconstruction_algorithm       ... other parameters
--------    ------------------------
1           0
1           0
1           0
1           1
1           1
1           1
1           2
1           2
1           2
1           3
2           0
2           0
2           0
2           0
2           1
2           1
2           1
2           2
2           2
2           2
2           3
2           3
...         ...

Desired Result
I'm trying to find a query which can (in one step) do the following:

Get selected entries of table t1 (e.g. id and class) for some rows which meet some filter criteria (e.g. counts between 100000 and 150000)
Get the maximum amount of entries withe the same reconstruction_algorithm which belong to a single entry in t1 (e.g for the entry with id 1 this would be 3)

The problem

Up to now we used two requests. The first was used to get the entries of t1 which meet the filter criteria:

SELECT class, counts FROM t1 WHERE parameterX <= 123 AND parameterY >= 20;

The second was used to get the according amount of entries from t2:

SELECT MAX(tm.tNum) FROM (SELECT COUNT(*) as tNum from t2 t, t1 q WHERE q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm;

Now I'd like to combine those two queries into one query. I tried doing this the following way:

SELECT q.id, q.class, MAX(tm.tmNum) AS nmbr
FROM t1 q, (SELECT COUNT(*) as tmNum from t2 t, t1 q WHERE
                q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm
WHERE q.parameterX >= 123 ORDER BY q.counts DESC;

The problem is now, that this query (tried in different ways) does return only a single row, although the outer query returns several rows. So I'd like the inner query to be run for every result of the outer query.

Is there a way to achieve this?

I hope somebody knows the answer to this problem.


texts: How do I replicate a database over 2 geographical sites?

texts:
Disclaimer: I have no idea what I'm talking about here, or if what I'm doing is the correct thing to do.

Long story short, my superior has asked me if it's possible to replicate our internal database on another SQL Server instance in another geographical site and keep them both in sync.

For context, they've recently expanded the business and have opened a new building (we'll call this Site B) about 15 miles away from the main building (Site A). Both sites are connected by a VPN.

At Site A, we have a MSSQL 2012 Standard install which has our internal database.

When users from site B are connecting to the database over the VPN, they're reporting that it takes nearly 5 minutes to establish a connection and open the client application. I'll be checking this tomorrow since I've never heard this problem reported before.

My superior seems to think the best idea would be to somehow replicate/mirror (I'm not sure of the terminology here) the database on the server in Site B and somehow keep them both in sync.

Is this what replication is for, and if so, where can I start learning how to replicate the database.

Otherwise, if this is a very bad idea, what other options do I have?

tl;dr - Can I have two copies of a database, kept in sync, in two different buildings?


texts: Create sequence based on the value of another column

texts:
I have to create sequence numbers based on related user. Like:

╔════════════╦═════════════╦═══════════════════╗
║ Row Id     ║ User Id     ║ Sequence Number   ║
╠════════════╬═════════════╬═══════════════════╣
║ 1          ║ 1           ║         1         ║
║ 2          ║ 1           ║         2         ║
║ 3          ║ 2           ║         1         ║
║ 4          ║ 2           ║         2         ║
║ 5          ║ 3           ║         1         ║
║ 6          ║ 1           ║         3         ║
╚════════════╩═════════════╩═══════════════════╝

I have some prerequisites:

Each sequence number sholud be between 1 and 9999. After 9999, it shoud leap to 1 and continue from there. (CYCLE property of Sequence)
Sequence numbers should be generated based on User Id. Each UserId should have its own sequence.
Sequence numbers should be sequencial. such that for User ID 5: Sequence # 123 sholud be followed by 124, leap or reuse of a number sholud not happen.

So using a Sequence looks pretty right to me. But I failed to add User ID distinction to sequence.

CREATE SEQUENCE [dbo].[seq_SequenceNumber]
AS [smallint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE
NO CACHE

How can I add UserId partition to this sequence?


texts:
We have a vendor working on Java (JDBC) application saying they are getting the error

  Connection Reset by Peer: Socket Write Error

They are insisting that the SQL Server (SQL Server 2012) is forcibly disconnecting their application server for some reason. I have run Profiler traces to collect Attention and User Error Message events and have found nothing helpful. I have checked the ring buffer and have only failed logins - no disconnects.

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Windows logs do not show anything helpful. Is this an error I should be able to track down in SQL Server, or is this purely a network error that will need a network trace or something else? Any ideas?


texts:
I have a problem.

In my access database I have two fields with DateTime. I use them for when a meeting starts and ends.

I would like to concatenate them for a report, but 09.00 ends up as 9.0 so my desired "09.00 - 10.15" ends up as "9.0 - 10.15".

Please help.


texts:
I have postgres on my pc and I work with it every day.

But today when I tried to connect to postgres, I did not manage to do it.

I decide to start it by typing systemctl start postgresql@10-main, by I have this output:

  Job for postgresql@10-main.service failed because a configured
  resource limit was exceeded. See "systemctl status
  postgresql@10-main.service" and "journalctl -xe" for details.

then I type

systemctl status postgresql@10-main.service

like he say, this is the output:

postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: failed (Result: resources) since ث 2019-10-22 14:09:28 CET; 15s ago
  Process: 9179 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start (code=exited, status=1/FAILURE)

أكت 22 14:09:28 mobelite-Inspiron-7577 systemd[1]: Starting PostgreSQL Cluster 10-main...
أكت 22 14:09:28 mobelite-Inspiron-7577 postgresql@10-main[9179]: Error: Config owner (postgres:121) and data owner (systemd-network:101) do not match, and config owner is not root
أكت 22 14:09:28 mobelite-Inspiron-7577 systemd[1]: postgresql@10-main.service: PID file /run/postgresql/10-main.pid not readable (yet?) after start: No such file or directory
أكت 22 14:09:28 mobelite-Inspiron-7577 systemd[1]: Failed to start PostgreSQL Cluster 10-main.
أكت 22 14:09:28 mobelite-Inspiron-7577 systemd[1]: postgresql@10-main.service: Unit entered failed state.
أكت 22 14:09:28 mobelite-Inspiron-7577 systemd[1]: postgresql@10-main.service: Failed with result 'resources'.

The Postgres logs say:

2019-10-22 09:33:19.718 CET [4588] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-10-22 09:33:19.718 CET [4588] LOG: listening on IPv6 address "::", port 5432
2019-10-22 09:33:20.062 CET [4588] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-10-22 09:33:20.528 CET [4590] LOG: database system was shut down at
2019-10-20 21:02:17 CET 2019-10-22 09:33:20.937 CET [4588] LOG: database system is ready to accept connections
2019-10-22 09:33:21.536 CET [4603] [unknown]@[unknown] LOG: incomplete startup packet
2019-10-22 10:06:18.170 CET [10958] [unknown]@[unknown] LOG: invalid length of startup packet
2019-10-22 10:41:37.536 CET [13788] [unknown]@[unknown] LOG: invalid length of startup packet
2019-10-22 11:00:13.521 CET [4588] LOG: received fast shutdown request
2019-10-22 11:00:13.561 CET [4588] LOG: aborting any active transactions
2019-10-22 11:00:13.565 CET [4588] LOG: worker process: logical replication launcher (PID 4599) exited with exit code 1
2019-10-22 11:00:13.566 CET [4594] LOG: shutting down
2019-10-22 11:00:13.699 CET [4588] LOG: database system is shut down

Can anyone help me please


texts: Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize?

texts:
I have a SQL Server 2008 machine that is running backed by a SAN (not entirely certain of the SAN configuration). I've been noticing that some queries are slow to respond, so have been running some tests trying to optimize/index the slow parts. I am not a SQL Server DBA (background is more MySQL centric), but have the task of improving this performance.

My tests are the following. I created 2 tables:

CREATE TABLE "Z_SIMULATION_0"
(
    "ID" INT NOT NULL,
    "VALUE" INT NOT NULL,
    PRIMARY KEY ("ID")
);

CREATE TABLE "Z_SIMULATION_1_TABLE"
(
    "ID" BIGINT NOT NULL,
    "ELEMENT" BIGINT NOT NULL,
    "flag" INT NULL DEFAULT NULL,
    "IS_ACTIVE" BIT NOT NULL DEFAULT b'0',
    PRIMARY KEY ("ID", "ELEMENT")
);

The indexes for the Z_SIMULATION_1_TABLE as exported by SSMS are:

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_1_IS_ACTIVE]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([IS_ACTIVE] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_INDEX_ACTIVE]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC, [IS_ACTIVE] ASC)
    INCLUDE ([ELEMENT], [flag])
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ELEMENT]
    ON [dbo].[Z_SIMULATION_1_TABLE]( [ELEMENT] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ID]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

ALTER TABLE [dbo].[Z_SIMULATION_1_TABLE]
  ADD CONSTRAINT [PK_Z_SIMULATION_1_TABLE]
  PRIMARY KEY NONCLUSTERED ([ID] ASC, [ELEMENT] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

I then populated the Z_SIMULATION_0 table with 600K random (non-repeating) ID/Value pairs (IDs are between 1 & 600k).

Finally, I executed this query:

INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE)
    SELECT 2, value, 0
    FROM Z_SIMULATION_0
    WHERE ID>=1 AND ID <= 500000;

This query took 14s to complete, which I find is significantly too long. I don't see why an insert into a blank table should take this long, and more importantly, I'm not sure how to optimize things to make it any faster.

.

I see the Sort (for the index I presume?) is taking the bulk of the time, but not sure what to do about that.

How can I improve the performance/reduce the amount of time it takes to do the insert?  I'm open to changing/modifying/deleting indexes as required or necessary.


texts: Migrating tables from SQL Server to MySQL

texts: I am using MySQL Migration Wizard to copy some tables from SQL server to MySQL local instance. When I define the source and target connection and test them I get a Connection Successful message and the Fetch Schema List step also displays finished retrieving the schema. However when I go to the next step, it does not display the schema! Has anyone faced the issue? Since the connections to the source is successful, I am not able to find out what the issue is. Thanks.


texts: DDLTrigger_Audit error "The user does not have permission to perform this action"

texts:
I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error.

database "XXX" failed on Jan 17 2019 10:36PM with error #297, severity 16",
state 1 in procedure/trigger "DDLTrigger_Audit" , line # 29 while processing object "sp_XXX" with the following message:
The user does not have permission to perform this action.

I have tried giving GRANT CONTROL SERVER permissions to the account executing the trigger and tried running the Stored procedure again. I got a different error this time.

The statement has been terminated.
Msg 8152, Level 16, State 14, Procedure sp_XXX, Line 182 [Batch Start Line 2]
String or binary data would be truncated.
Mail (Id: 6500) queued.

I am confused if the issue is with a stored procedure or with the trigger.


texts: How to simplify SSAS deployment from DEV to PRO environment

texts:
Here is my issue: In my company, there is a team of developper who creates cubes, dimensions(...) in SSAS in a DEV environment (let's call it SSASDEV). This environment is bounded to a SQL Server database in DEV (let's call it SQDEV).
My job is to deploy their work from DEV environment to PRO environment. This PRO environment (SSASPRO) is based on another SQL Server database (SQPRO).

For now, the developper team script the SSAS, send me the XMLA script, I need to change every security rule defined and the connection string specified in this XMLA (security rules because it's based on different Roles depending on the environment and the connection string because of the SQL Server database).
It's a heavy work to do for each deployment so I want to automatize this.

The only ways I found is

(1) - to script the DEV cube and apply the XMLA on PRO (what is done now).

(2) - to synchronize cubes (which means, unprocess, re-apply security rules and modify connectionstring).

I can not imagine I'm the only one in this situation in the entire world!
Does anyone has a tip or a hint for me? Does another simpliest way exists and I missed it?
Is my intern organization (differents databases for each environment) not logical for SSAS?

I work with SSAS 2008R2 and 2012

Thanks a lot for your responses!


texts:
I'm new here - if I missed anything in the question please let me know.

I'm trying to build a people directory like LinkedIn has:

http://www.linkedin.com/directory/people-a

I don't want to fetch all the rows with name field starting with A and build the link list like LinkedIn. Is there any way in MySQL so I can only fetch rows in this sequence:

1st, 100th,101st,200th,201st,300th,301st,400th,401st, Last

That means I am trying to get two consecutive rows after a certain gap including the first and last item. The ids are not in nice uniformly increasing order.

Say my initial query SELECT * FROM businesses where name like 'a%' order by name returns ids like this:

1,3,5,6,8, 9,12,33,45,66,77,88,100,103,120,133,155,166,177,178,198

Above is if I want to get all the rows. But instead I'm trying to get only the items after a certain distance. For example if I want to pick after every 5 items:

1,(skip 4),9,12,(skip 4), 88,100,(skip 4),166,177,(skip 1),198

So skip 4 items and take next two. Is this possible without fetching all the rows?

I'm not sure if the above is the best way to achieve what I'm looking for. If you have better solution please let me know.

SELECT * FROM businesses where name like 'a%' order by name this query returns about 50K rows. But I don't need all of them. I'm trying to build people directory that is nested several levels. So, on the first page I'll show links like this:

Page1: http://example.com/people/a
Links:
   Adam - Alan => http://example.com/people/a/1   //here I skipped say 1000 people after Adam and before Alan
   Albert - Amy => http://example.com/people/a/2   // Here I skipped another 1000 people
   ...

Page2: http://example.com/people/a/1
    Here I show all those 1000 people profile links that I skipped in page1.

This is only two level. If you visit the above linkedin link, you'll see 3 or 4 levels of nesting.


texts: Elastic queries available in SQL Server 2017

texts:
Are elastic queries available in SQL Server 2017?

Can see them documented for Azure SQL:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

but nothing for SQL Server. My assumption is that it's not available.


texts:
I'm searching for UK postcodes within a radius of a given postcode.  The query that I'm using looks like this:

SELECT *,
    earth_distance(ll_to_earth( {$lat}, {$lon} ), ll_to_earth(lat, lng)) as distance
FROM postcodes
WHERE earth_box(ll_to_earth({$lat}, {$lon}), {$meterDistance}) @> ll_to_earth(lat, lng)
    AND earth_distance(ll_to_earth( {$lat}, {$lon} ), ll_to_earth(lat, lng)) <= {$meterDistance}

Where the {$variables} are replaced with the proper numeric values at runtime.

I have already created an index for ll_to_earth(lat, lng)) on my table.

I want to know if the Postgres parser is intelligent enough to calculate earth_distance(ll_to_earth( {$lat}, {$lon} ) only once or if it is going to be repeating the same calculation on every row.

Obviously if it is going to repeat the calculation on each row then I would like to find a way to optimize this (any suggestions?).

The reason that I have the AND clause is because I'm using earth_box and require a radius.  I do require the distance between the postcodes to be returned.

----- Update in response to query -----

It's PHP magic inserting the values before sending the query.  An example of the query is:

SELECT *,
   earth_distance(ll_to_earth( 51.66628422683990000, -0.61470728159074400 ), ll_to_earth(lat, lng)) as distance
FROM postcodes
WHERE earth_box(ll_to_earth(51.66628422683990000, -0.61470728159074400), 4828.02) @> ll_to_earth(lat, lng)
  AND earth_distance(ll_to_earth( 51.66628422683990000, -0.61470728159074400 ), ll_to_earth(lat, lng)) <= 4828.02

The EXPLAIN ANALYZE looks like this:

"Bitmap Heap Scan on postcodes  (cost=173.59..6777.31 rows=568 width=48) (actual time=20.785..210.522 rows=1202 loops=1)"
"  Recheck Cond: ('(3950943.86394944, -47269.8036202945, 4998280.46727446),(3960599.90371891, -37613.763850828, 5007936.50704393)'::cube @> (ll_to_earth((lat)::double precision, (lng)::double precision))::cube)"
"  Filter: (sec_to_gc(cube_distance('(3955771.88383417, -42441.7837355612, 5003108.4871592)'::cube, (ll_to_earth((lat)::double precision, (lng)::double precision))::cube)) <= 4828.02::double precision)"
"  Rows Removed by Filter: 969"
"  ->  Bitmap Index Scan on postcode_gis_index  (cost=0.00..173.45 rows=1704 width=0) (actual time=2.910..2.910 rows=2171 loops=1)"
"        Index Cond: ('(3950943.86394944, -47269.8036202945, 4998280.46727446),(3960599.90371891, -37613.763850828, 5007936.50704393)'::cube @> (ll_to_earth((lat)::double precision, (lng)::double precision))::cube)"
"Total runtime: 210.842 ms"

I have only really worked in mySQL before and I'm not sure how to interpret the analysis.  I have looked for documentation but it doesn't really help me to answer whether the common functions are being calculated repeatedly.

How would I structure the query so that I could generate earth_distance(ll_to_earth( 51.66628422683990000, -0.61470728159074400 ), ll_to_earth(lat, lng)) as a constant and calculate it just once?


texts: Improving the performance of a long query

texts:
I have a query that i have let run for a few hours.  I am forced to kill the query because it is taking up 60% of the CPU.  Is there anything i can do to improve its performance?

select distinct(random_selection.randnum),
    random_selection.dropper_id,
    random_selection.ozip3
from random_selection
where random_selection.dropper_id is not null
and random_selection.quarter = 121
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq
    from forecast_entry, production_weeks
    where forecast_entry.week = production_weeks.production_week
    and production_weeks.project_cd = 'EXFC'
    and production_weeks.yyq >= 121)

union

select distinct(random_selection.randnum),
    dropper_city_brk_2.dropper_id,
    random_selection.ozip3
from random_selection, dropper_city_brk_2, dropper
where random_selection.ozip3 = dropper_city_brk_2.zip3
and dropper.dropper_id = dropper_city_brk_2.dropper_id
and dropper.active = 1
and dropper_city_brk_2.dropper_id <> 10002
and random_selection.quarter = 121
and random_selection.dropper_id is null
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq
    from forecast_entry, production_weeks
    where forecast_entry.week = production_weeks.production_week
    and production_weeks.project_cd = 'EXFC'
    and production_weeks.yyq >= 121)

Query explained:

the main objective is to get all of the randnum, dropper_id, and ozip3 from random_selection table that are not in the forecast_entry table and are in yyq 121 and have a project code of EXFC.  yyq is retrieved from the production_weeks table by associating week and production_week. Some dropper_id are null so we need to pull that data from the dropper_city_brk_2 table by associating ozip3 and zip3.  We dont want dropper_id that are inactive so they must have active equal 1, this is by associating the dropper table.


texts: Galera cluster node fails with error. Brand new cluster + master / node. Access denied or can't connect

texts:
I am having issue.

Previous cluster fatally crashed, so I've created a new one with pretty much same settings (changed cluster name only).

Here is a galera.cnf for a node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="cluster"
wsrep_cluster_address="gcomm://y.y.y.139, x.x.x.25, 10.5.2.1"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="x.x.x.25"
wsrep_node_name="xxx002"

Galera.cnf for master differs only in wsrep_node_address (y.y.y.139) and wsrep_node_name (yyy001).

I have add permissions for debian-sys-maint but as soon I connect to master node, I will get:

Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: 1.0 (xxx002): State transfer from 0.0 (yyy001) complete.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Member 1.0 (xxx002) synced with group.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: Synchronized with group, ready for connections
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 11 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 12 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 13 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Of course I won't be able to login to mysql as I will get:

  ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

I have no idea what is happening. It worked fine (I had to reinstall all servers, but I am using same template).

I cannot create cluster for 4 days now :(

I have added those as soon I've installed MariaDB on the node:

GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost IDENTIFIED BY  'your password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'PASS';
CREATE USER 'admin'@'10.%' IDENTIFIED BY 'PASSS';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

The master data was restored from .sql (backup from a previous cluster).

Not sure what it could cause it ?


texts: Why does the Select statement stalls when executed directly in sequence?

texts:
When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go.
If the table is MyIsam, it works ok. If executed  [edited] the very first time a  few seconds later (5-10s) it executes in 15 ms, no cache involved.
Anyone can explain this behaviour ?

DROP TABLE IF EXISTS tmp_vlanendpoints;
CREATE TABLE tmp_vlanendpoints (
  routeId INTEGER,
  INDEX routeId (routeId)
)
SELECT
  routeId,
  HostNameOrIp,
  CONCAT(
      HostNameOrIp,
      '»ifIndex=',
      IFNULL(subIfIndex,ifIndex)
  ) AS port_sourcerefcode,
  nodeType
FROM tmp_getdatamultipointtrails
WHERE nodeType='PE'
    OR (numEDDs=1 AND nodeType='EDD')
    OR (numEDDs=0 AND nodeType='AGG' AND linkId IS NULL);

# Failing SELECT when executed in sequence. Takes 15 ms if executed alone.
SELECT
    veacpe.HostNameOrIp AS cpe_a,
    veacpe.port_sourcerefcode AS port_acpe,
    veape.HostNameOrIp AS pe_a,
    veape.port_sourcerefcode AS port_ape,
    vezcpe.HostNameOrIp AS cpe_z,
    vezcpe.port_sourcerefcode AS port_zcpe,
    vezpe.HostNameOrIp AS pe_z,
    vezpe.port_sourcerefcode AS port_zpe,
    evc.*
FROM tmp_evcdata evc
JOIN tmp_vlanendpoints veacpe ON veacpe.routeId=evc.routeId_a AND veacpe.nodeType<>'PE'
JOIN tmp_vlanendpoints veape ON veape.routeId=evc.routeId_a AND veape.nodeType='PE'
JOIN tmp_vlanendpoints vezcpe ON vezcpe.routeId=evc.routeId_z AND vezcpe.nodeType<>'PE'
JOIN tmp_vlanendpoints vezpe ON vezpe.routeId=evc.routeId_z AND vezpe.nodeType='PE';

SHOW CREATE TABLE:

CREATE TABLE `tmp_getdatamultipointtrails` (
   `routeId` int(11) DEFAULT NULL,
   `linkId` int(11) DEFAULT '0',
   `deviceId` int(11) DEFAULT NULL,
   `nodeType` varchar(10) DEFAULT NULL,
   `numEDDs` decimal(23,0) DEFAULT NULL,
   `numLooseEnds` decimal(23,0) DEFAULT NULL,
   `leafNE` text,
   `numNEs` bigint(21) NOT NULL DEFAULT '0',
   `missingTopology` text,
   `vlanId` int(11) DEFAULT NULL,
   `CIR` bigint(20) NOT NULL DEFAULT '0',
   `SourceRefCode` text,
   `HostNameOrIp` varchar(255) NOT NULL,
   `serverTrailSourceRefCode` text,
   `entPhysicalIndex` int(10) unsigned DEFAULT NULL,
   `ifIndex` int(10) unsigned DEFAULT NULL,
   `subIfIndex` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

  count(*): 19733
  select * from tmp_getdatamultipointtrails limit 2;

17 1340    821 PE  1   0   [3122]  3   ''  926 9984    PE821»if284»vlan926 89.13.237.13    10.25.153.4»ifIndex=6»to:89.13.237.133»ifIndex=284  17105425    284 576
17  4701    3122    EDD 1   0   [3122]  3   '' 926  9984    PE821»if284»vlan926    10.250.153.3 10.25.153.3»ifIndex=17»to:10.25.153.3»ifIndex=101720064     7001    101720064   NULL


texts: Create stored procedure to select the data?

texts:
I have four tables user, shops, offer, merchant in that id is unique and present in all table

shop
------
id  shops
1    shop1
1    shop2
1    shop3

user
-----
id   name
1    abc

merchant
---------
id   date      business
1   2012-6-6    Mall

offer
------
id   status
1    true
1    true
1    false

First select id from shop table then match with user to get username, then match with merchant table to get date, business.

Then match with shop table to get shops (count the shops that how much that id have), then match with offer table to get status (count the status=true that how much that id have)


texts: Which Azure Storage / Database Technology to make use of for large scale logging

texts:
I am looking into making use of Azure hosting services in order to develop a logging service for our company.

Azure has a number of storage options available (I have listed links below). However I am not sure which one would be best suited for my needs. I am coming from a MS SQL Server background so I am probably not as open minded as I should be, hence my question here is to try and explore other more suitable options.  If any one knows of any place where I can get design help specifically for azure please let me know, as I have not been able to find any.

Requirements:

Background: We are looking to log messages from a variety of types of devices which the company owns. This needs to be on a mass scale. Several million entries per day. As much as I have tried to keep the design a fixed structure, due to the variety of device types out their they messages differ completely and have different elements. I would somehow like to still be able to query message specific attributes. (How one would do this, I do not know).

All messages come into this logging service as A JSON object. The JSON object obviously varies based on the type of device which sent the message and the type of message the device is sending.
There will be some common attributes such as deviceID, LogTime,customerId etc.
Due to the mention of above, it seems that a document / object store technology would suit the application better. However as mentioned above I have not have much exposure to these types of technologies. Will it be able to efficiently handle the load, and queries etc.
We will need to be able to store months of log values without slowing down daily queries to a halt.
Data analytic's and big queries should be able to be done on the data after some time of running. I'm not sure if one would make use of a different mechanism for this? Possibly a sort of storage layer and then a big data layer. However my storage mechanism needs to keep this future features in mind.

Azure offers the following storage and database services:

Sql Database
DocumentDb
Storage: Blobs, Tables,Queues,Files and Disks
StorSimple Hybrid cloud Stroage
SQL Data Warehouse

I am sorry, I know this question is quite general and possibly open ended. I am not looking for anyone to just give the the complete solution in my hand. I am looking for some guidance in the right direction and experience from people who have already gone down this road before.


texts: Replicate/Mirror SQL Server schema to slave databases

texts:
I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database.

MASTERDB will be always empty, as I only need the schema. No data is going to be replicated/mirrored.

The scenario:

I create a new table on MASTERDB and I want that table to be created on those 10 tables automatically
If I change a field on MASTERDB, replicate the changes for all the other databases

I have looked at Mirroring and Replication, but I could not determine if these will do what I want.

Thanks in advance


texts:
In a SQL Server 2016 environment, I have a SELECT query which queries one table, which joins to itself on 2 indexed columns (both non-clustered):

SELECT
    A.REFERENCE,
    A.MEMBERID
FROM
    TRANS A INNER JOIN
    TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID = B.MEMBERID

This one above returns in about 2 seconds.

But, when using the same query, but looking for matching references across different memberids e.g. changing = to <>, it takes about 20 seconds.

SELECT
    A.REFERENCE,
    A.MEMBERID,
    B.MEMBERID AS MEMBERID_B
FROM
    TRANS A INNER JOIN
    TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID <> B.MEMBERID

I realize that this 2nd query uses a negative comparison and thus, must fully review table B vs. the 1st query which can just find the single match and move on (I think). In the query plan, it uses the same index and both are seeks, but the = estimates a single row involved, and the <> estimates 20 million rows (entire table).

My question is: without being able to alter any indexes or table structure, etc. how might I go about optimizing the query with the negative comparison? (or re-writing it to achieve the same results?) I've searched google, and found lots of info saying not to use negative comparisons where possible, but not much on optimizing when you must.

Unfortunately I can't post the exact code or anything from the db environment because I am at work and we're not permitted to post any actual code etc. e.g. Clipboard won't even work across the remote connection and can't use a browser in the machine I am accessing the DB from. My example is a dummy example to mimic the similar situation. I realize the best way is to examine indexes, structure, and query plans, but I was hoping to learn some general advice for the situation.


texts: Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?

texts:
Consider:

SQL> select sysdate from dual;

SYSDATE
-----------
22-Aug-13 0

SQL> select sysdate from dual; select sysdate from dual;

select sysdate from dual; select sysdate from dual

ORA-00911: invalid character

SQL>

Notice that when I execute two queries at once in the command window, it throws the ORA-00911: invalid character error which generally indicates syntactic error.


texts: PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

texts:
I have to restore a DB table every day from a postgresql 8.4 to 8.1.
For the backup, I use this code (windows server):

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > D:\backup\myFile.sql

I send "myFile.sql" through SCP to the second server, I restore it with the below command via a console (centos) :

su - postgres

psql myDB

drop my_table;

\q

psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql

exit

Because of the version problem, I can't use pg_dump and pg_restore. I want to put this code in a shell script and execute as cron task, but I don't know how to do. I have tried with psql:

su - postgres -c "psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql"

But many errors occurred like:

 duplicate key violates unique constraint
 ERROR:  relation "site_idx" already exists
...

It would be better to drop the table, create a new and restore with psql but I don't know how to do it. Any ideas?


texts:
After I upgraded MySQL from 5.6 to 5.7 on Ubuntu 14.04, I started to have this problem on certain action. As I understand, the problem is that I am trying to get row that is too big from the table.
So, I managed to solve this problem by limiting columns on select part which was originally using select *.

However, I want to solve this problem by changing the setting. (Because I might be able to face a situation where many columns for select are required)

I have tried to change

innodb_strict_mode = 0
max_allowed_packet = 512M
innodb_log_file_size = 128M
innodb_log_buffer_size = 256M
innodb_file_per_table = ON

all these values but without any luck.

Any advice or suggestion would be appreciated. Thank you


texts: Find closest match in two columns, one containing wildcards

texts:
no script to attach as I can pull the data just fine, I am struggling to join my two tables though.
The column I want to join on has the same information formatted differently in each table, making it worse, one of these columns uses wildcards for the program that takes data from there.
Example of what I mean:
T1.Col1 would contain the actual value, say EXAMPLE   A , EXAMPLE   B , EXAMPLF A
T2.Col1 would have one entry like EXAMPL*[AB]
The Program using that table would treat all values the same if it started in EXAMPL, and ending in EITHER A or B.
Meaning the following would all be a match, this is what I need to recreate for my script:
EXAMPLE A , EXAMPLE B , EXAMPLF A
Can I recreate that logic in SQL? To find a match/closest match based on certain conditions? T2 Column contains about 200 values just like that and all different, so I need a solution that can apply to everything and not just this case.
I've tried formatting T2 values using Replace to be more like T1 but the square brackets are killing me. Making new rows in the output table might help but we're at the limit of what I can do in SQL.


texts: Securing a MySQL database with more than just username and password

texts:
We have a web app that runs in Azure and use a MySQL database, also in Azure, for its data storage. We store PII data from the various companies that use our system in this database.

The web app is written in PHP and connects to the database using a username and password. We are needing to comply with a big customers data protection requirements, and they require any access to PII data to be 2-factor authenticated.

I have no idea where to start with this or if it is even possible. Does this mean for every connection the web app makes (tens of thousands a minute), it would need to some how generate a 2-factor code as well each time??? Assuming adding 2-factor is even possible for a MySQL server??(I have looked around and can't find anything).

So my questions are:

Is 2-factor possible with MySQL
If not, how else can it be
secured so it requires more than just a username and password to
connect (that me using workbench, and our web app can do).

Thanks


texts:
I'm having a problem creating a MySQL udf on my server (I was able to do it locally on OS X). I am compiling it like this on the server:

gcc $(mysql_config --cflags) -I /usr/include/mysql -I. -shared udf_median.cc -o udf_median.so

Here is the output of mysql_config --cflags

mysql_config --cflags
-I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables  -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DMY_PTHREAD_FASTMUTEX=1

So from what I can tell, MySQL is compiled for 32bit (it was done via x86_64 RPM). I had to install a bunch of different packages to get it to compile (32 bit packages, I think).

When I try to do:

CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

I get:

Can't open shared library 'udf_median.so' (errno: 11 /usr/lib64/mysql/plugin/udf_median.so: wrong ELF class: ELFCLASS32)

From what I can tell this is a conflict between 32 and 64 bit but I'm not sure on how to fix it. I tried to put the .so in /usr/lib/mysql/plugin/ however MySQL doesn't seem to be recognizing that directory.

Anyone have any recommendations?

Edit

So if I try to compile it without the mysql_config like this:

gcc -shared -fPIC -I /usr/include/mysql -o udf_median.so udf_median.cc

I get these errors:

In file included from udf_median.cc:36:
/usr/include/mysql/m_string.h: In function ‘const uchar* skip_trailing_space(const uchar*, size_t)’:
/usr/include/mysql/m_string.h:261: error: cast from ‘const uchar*’ to ‘intptr’ loses precision
/usr/include/mysql/m_string.h:263: error: cast from ‘const uchar*’ to ‘intptr’ loses precision
/usr/include/mysql/m_string.h:265: error: cast from ‘const uchar*’ to ‘intptr’ loses precision


texts:
We have a need to move our Moodle server to a different host, and our current host runs a LAMP stack that likely runs on a VM.  It is accessed most of the time.  In the event that the current host lets us take a copy of the VM, we will just take the copy of the vm and reconfigure it for our new host; but if that doesn't work out, then we will need to replicate the database since it doesn't have much downtime when users are not accessing the system.

We did something similar with our exchange server when we cut it from a local exchange server to Office 365; just replicated it until everything was working smoothly and then cut over the DNS records when everything was working correctly.

If we do the replication of the MySQL database, it may require us to replicate the changes between three database servers, since the one it is now hosted on only opens the database port to our local network, and therefore the two sites could not communicate directly; so we'll likely need a third-party to copy them through, probably a MySQL server on the local network.

Does this sound feasible or necessary?


texts: use of crows foot or chens notation to draw an extended ER diagram

texts:
I am trying to understand if we can use crows foot or chens notation to get an extended ER diagram.

or does extended ER diagram has its own notations that should be used ?

From what i understand, crows foot or chens notations do not support class/subclass, inheritance/generalization representations using their existing items.

Please correct me if I have got the concept of ER and EER diagrams wrong.


texts: Queries running parallel with default maxdop

texts:
I need to understand below behavior of sql server queries

System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance.

Currently MAXDOP is 0 and ctop is 5

This is a read only instance acting as stand by for LS where all reporting is done.

Lot of queries coming in seems to show running with degree of parallelism 64.

Questions:
.1. why degree of parallelism is not 80 when no of logical processors is 80?

Is my understanding correct that if there are 6 slow queries running over an hour all showing degree of parallelism 64 would be expected to slow as they might be hitting same threads and thus showing as cx packet waits. Hence I believe changing MAXDOP to say 16 or 8 by testing should give them their own threads to complete?

Thanks


texts: MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL

texts:
Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:

SELECT table1.id FROM table1 WHERE table1.a='FOO' AND
NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

or

SELECT table1.id FROM table1 LEFT OUTER JOIN table2
ON table1.id = table2.id WHERE table1.a='FOO' AND table2.id IS NULL;


texts:
I am new to influxdb and I am having this trouble to show measurements.

Our setup is that prometheus(aws ec2) is writing data to Influxdb(aws ec2). When I execute 'SHOW MEASUREMENT' I cannot see anything. However, when I execute 'SHOW SERIES' I can see lot of time series.

Is there anyone who knows why I am seeing this? Thank you in Advance


texts:
I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board.

I have one table created with all the users and a unique ID for each.

How do I link up the other 3 tables with the unique ID so the correct information is linked up to each user in each of the tables?

I understand setting relationships, but can't figure out to what field I link up the ID field in the users table to the other tables.


texts: How can I escape XML field in XML-output query?

texts:
I have following request:

SELECT XMLAGG(data) FROM
(SELECT XMLELEMENT(name item, XMLFOREST(
   title,
   body as description,
   date_created as date,
   'http://example.com/news'||id as link
)) AS data FROM NEWS) AS smthng;

The issue is that body is actually XML itself, so the question is -
How can I escape XML field (changing < to &lt; > to &gt; etc.) in this query?


texts: Why Does ad_zd_table.upgrade Convert Triggers To Point at the # View?

texts: Why does the ad_zd_table.upgrade function (used to notify editioning of modifications in a table schema) automatically change the database triggers to point at the # view of that table? (In our case the result is that the triggers stop firing. I checked and they are editioning views that in theory support triggers, but they aren't working.) Is there a way to work around this problem?


texts: PostgreSQL FTS for quoted xml tags - is it possible?

texts:
I use Pg FTS to text-search database containing some technical descriptions/comments. Which in particular happen to contain XML snippets.
Those are amazingly difficult to find by XML tags. I found basic workaround (to find tags exactly-as-written) but look for suggestions how to improve.

Default state

CREATE TABLE mytab(id INTEGER, descr TEXT);
INSERT INTO mytab VALUES(1, 'Please create table');
INSERT INTO mytab VALUES(2, 'Please add <some-tag/> to config file');

SELECT id FROM mytab where descr @@ 'add';
    2
   (1 row)

SELECT id FROM mytab where descr @@ '<some-tag/>';
   (0 rows)

SELECT id FROM mytab where descr @@ 'some-tag';
   (0 rows)

SELECT ts_debug('add <some-tag/>');
   (asciiword,"Word, all ASCII",add,{english_stem},english_stem,{add})
   (blank,"Space symbols"," ",{},,)
   (tag,"XML tag",<some-tag/>,{},,)

Finding tags as exactly-written

I managed to improve things a little bit by:

CREATE TEXT SEARCH CONFIGURATION xmlfixed(COPY = english);
ALTER TEXT SEARCH CONFIGURATION xmlfixed ADD MAPPING FOR TAG WITH simple;

SELECT id FROM mytab WHERE TO_TSVECTOR('xmlfixed', descr) @@ TO_TSQUERY('xmlfixed', 'add');
      2
    (1 row)

SELECT id FROM mytab WHERE TO_TSVECTOR('xmlfixed', descr) @@ TO_TSQUERY('xmlfixed', '<some-tag/>');
      2
    (1 row)

but this works only if tag is written exactly. I still have

SELECT id FROM mytab WHERE TO_TSVECTOR('xmlfixed', descr) @@ TO_TSQUERY('xmlfixed', '<some-tag>');
    (0 rows)
SELECT id FROM mytab WHERE TO_TSVECTOR('xmlfixed', descr) @@ TO_TSQUERY('xmlfixed', 'some-tag');
    (0 rows)

(ok, ok, internally there were GIN indexes and reindex, but that's orthogonal)

Looking for help

Is there any way to configure this search so record 2 can be found when querying user asks for some-tag? Is it possible without writing new binary extension?

I suppose I could try implementing new custom dictionary which would strip <>/ characters, and use it together with simple as mapping for tag (codewise sth similar to  https://stackoverflow.com/questions/27993032/custom-postgres-fts-dictionary, and above that ALTER TEXT SEARCH CONFIGURATION xmlfixed ALTER MAPPING FOR TAG WITH xmlstripper, simple;, where xmlstripper would be this new extension). Still, distributing new binary extension seems very difficult (if at all possible) at organizational level.

Mayhaps there is some other way?


texts: Efficient, up-to-date way to import an Oracle database into MySql

texts:
Here's the situation: I need to automatically merge data from an Oracle and a MySql server into a single, new MySql server. The merging will happen daily, and the resulting server will be hosted on a separate machine that will be used for all further queries, reducing the load on the original servers.

I'll use mysqldump for the MySql portion, but I'm hesitant to decide on a method of importing the Oracle data. Many companies sell tools for this purpose, but I need to do this on a budget of $0.

The posts I found on Google contained mostly commercial solutions, with very little discussion about non-commercial ones. Since this is a task that will run daily, I want to be sure I'm using the best method, but I won't be able to make the correct choice without enough information.

I'm not looking specifically for a pre-made product, only a solution to my problem (ie. if the solution involves scripting, I'm happy to write a few lines of Python given some pointers). Any method of importing Oracle data into MySql is worth consideration. What I'm looking for is a general direction of where I should be heading, be it a pre-made product or a roll-your-own solution.

My Oracle data consists of mostly integers, dates, and text, which includes a lot of Chinese characters. There are also URLs scattered throughout and unique IDs (which I will need to convert to autonumbers). This may/may not change in the future, so a flexible solution is preferred.

Google's search results turn up outdated by at least three years - since this is a task that will be running daily, I want to make sure I'm using the best/most recent method. What's an up-to-date, efficient method of importing data from Oracle to MySql?


texts:
(I use sql server 2015)
In my Orders table Postal Code is a varchar.

I want to do a search like this:

SELECT distinct email
  FROM [dbo].[tb_Order]
  where postalCode in (20004,20528,20260,20529,22060,22181,20708)

(with many more zip codes)

I do not want to have to convert the list of codes into ("20004","20528","20708").

What is the best way to handle this?


texts:
Table:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5002       2006  -140
300C           5003       2007  -150

I want to return the MAX Trading Id based on the subsection, I want this:

SubSection | TradingId  | Year  | Amount
100A           5001       2004  -120
200B           5001       2005  -130
300C           5003       2007  -150

I tried selecting MAX with a GROUPBY Subsection, tried HAVING MAX etc but no luck


texts:
I have two Tables
TABLE 1
ID       Name
1001     XYZ
1002     ABC

TABLE 2
ID    GAME
1001   A
1001   B
1002   A

I want the outcome of two tables join as
ID   NAME   GAME1    GAME2
1001 XYZ     A        B
1002 ABC     A

I tried with a simple join and it gives me multiple rows for 1001 and 1002
Query
SELECT t1.ID, t1.Name, t2.Game
FROM t1, t2
WHERE t1.ID = t2.ID

1001  A
1001  B
1002  A

NA

Actual O/P
1001  A
1001  B
1002  A

Expected O/P
ID   NAME   GAME1    GAME2
1001 XYZ     A        B
1002 ABC     A


texts: Single node availability group - is it possible?

texts:
Is it possible to have an availability group with just one node in it?

The reason I ask is because I am considering a distributed availability group spanning two single node always on availability groups thus getting away from the requirement for a WSFC. The two nodes being several miles apart which may mean a normal availability group may be problematic with data synching.


texts: Why did error messages change after upgrading to MySQL 5.7.9?

texts:
I am running MySQL 5.7.9 on a Windows machine, upgraded recently from a 5.6.17.

I used to get explicit error message with my previous versio, but somehow since the upgrade I get very mysterious error message. For example I get:

SQLSTATE[42S22]: Column not found: 1054 Unknown error 1054

While I used to have something like stated in the doc:

SQLSTATE[42S22]: Unknown column '%s' in '%s'

It's way easier to debug when you know which column was not found, and where (in 'select list' or 'where clause', etc.).

My question is: how to get back to explicit error message? There must be a parameter somewhere but I haven't manage to find it so far.

Thanks!


texts:
Is an UPDATE query in MySQL guaranteed to execute the field updates in order that are presented?

e.g.

UPDATE `table` SET `first_to_update` = `second_field`,
`second_field` = `third_field`, `third_field` = 5


texts: Search, filter and group across all columns in a tall and wide dataset quickly

texts:
We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows.

For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users to pick the fields they want, and perform filtering on any of these fields. In addition, they should also be able to group by any number of these fields (nested groups), sort by any of these fields, and perform aggregations across some of the grouped fields (sum, count, avg, min, dates).

Our source data is spread across several tables in SQL Server and while the above is possible by dynamically generating the SQL in code, it is slow. Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow; specifically grouping by multiple columns is slow, and adding another column to group by can add a few seconds to the query time.

Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.

In what way can I accomplish this?

To be specific, we will be building an interactive grid in AngularJS which calls out to an API with the fields the user wants to query, group, filter, sort on. The API is in C# and its current backend is SQL Server, but we can look at something else if needed. It would be nice to have the query results return in less than 30 seconds.  Because it is web based, there will be concurrent users.


texts: Cannot alter MYSQL Table (stuck in a invalid default value loop)

texts:
Some how I have 2 columns with incorrect default values.
Not sure how it got created in the first place as I imported the db from another source.

Problem now is, I cannot make any changes to the table cause MYSQL will complain:

  MySQL said: Invalid default value for 'campaign_start_date'

if I tried adding a column.

But when I try changing the DEFAULT VALUE to CURRENT_TIMESTAMP for campaign_start_date, it complains campaign_end_date has an invalid default value.

So I'm stuck in a loop that doesn't allow me to make any changes to the table.

Is there anyway to fix this?


texts: Cannot set character_set_database and character_set_server to utf8mb4

texts:
I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set.

Preliminary Notes:

I'm using Xampp (with MySQL 5.6.24). Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\xampp\mysql\my.ini C:\xampp\mysql\my.cnf, and none exists at the moment.
character-set related variables are as follows before creating any option file:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | cp850             |
| character_set_connection | cp850             |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | cp850             |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | cp850_general_ci  |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

Now I convert one schema to utf8mb4 (one ALTER SCHEMA and multiple ALTER TABLEs), everything works fine. Then I create the following C:\xampp\mysql\my.cnf file:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Next step, I stop mysql with xampp control panel, start it again, and go check the character-set related variables:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | latin1             |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | latin1_swedish_ci  |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+

According to the guide I follow, character_set_database and character_set_server should be set to utf8mb4, and collation_database and collation_server to utf8mb4_unicode_ci. What am I missing?

I have already tried to move my my.cnf option to other paths among those read by MySQL (see above), but nothing changes.


texts:
I am designing a data warehouse for a retail company.
The ERP stores prices by having a FromDate and a ToDate and these dates can overlap. The rules for getting the correct price is basically find the records that a certain date may be valid for, order by the primary key ID and the most recent is the one to use.

Our current data warehouse stores the values in the same manner, and then there is a UDF that runs over this table to find the most recent price. The performance of this is not great.

The new data warehouse that I have designed basically stores a record for each day for each price, so that when you want to query pricing, you simply do a join onto the Price table. The partitioned tables currently have just over 1 billion records. Performance isn't too bad.

The problem I'm now having is that each price could possibly have a Backup price. If the price for that Pricing Scheme is null or 0, then we have to look into the same table for the price against the backup price scheme.
This is where the performance is going down the drain.

A query like this is what I am currently doing:

SELECT
  COALESCE(NULLIF(FactProductPrice.OriginalPrice, 0), BackupProductPrice.OriginalPrice)
.......
    LEFT OUTER JOIN
      dbo.FactProductPrice ON
      FactProductPrice.PriceDate = FactStock.StockDate
      AND FactProductPrice.ProductId = FactStock.ProductId
      AND FactProductPrice.SalesPriceSchemeId = MatWarehouse.SalesPriceSchemeId
    LEFT OUTER JOIN
      dbo.FactProductPrice AS BackupProductPrice ON
      BackupProductPrice.PriceDate = FactStock.StockDate
      AND BackupProductPrice.ProductId = FactStock.ProductId
      AND BackupProductPrice.SalesPriceSchemeId = DimSalesPriceScheme.BackupSalesPriceSchemeId

I'm willing to completely redesign how these are stored, but I'm struggling to think of a better way. I don't have any real requirement to keep historical pricing (no SCD requirements).

Is there any best practice or accepted way to storing range like data?

    CREATE TABLE [dbo].[FactProductPrice2016](
        [PriceDate] [date] NOT NULL,
        [ProductId] [int] NOT NULL,
        [SalesPriceSchemeId] [int] NOT NULL,
        [CurrentPrice] [numeric](9, 2) NULL,
        [OriginalPrice] [numeric](9, 2) NULL,
    PRIMARY KEY CLUSTERED
    (
        [PriceDate] ASC,
        [ProductId] ASC,
        [SalesPriceSchemeId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FactProductPrice2016]  WITH CHECK ADD  CONSTRAINT [CK__FactProductPrice2016] CHECK  (([PriceDate]>='20160101' AND [PriceDate]<='20161231'))
GO

ALTER TABLE [dbo].[FactProductPrice2016] CHECK CONSTRAINT [CK__FactProductPrice2016]
GO

ALTER TABLE [dbo].[FactProductPrice2016] ADD PRIMARY KEY CLUSTERED
(
    [PriceDate] ASC,
    [ProductId] ASC,
    [SalesPriceSchemeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


texts:
Essentially, I have googled around but I am unsure what would this type of relationship be called. Table 3 contains a field [Labelled as STRING], that has multiple 1 to many relationships to Table 2 [Labelled as STRING and STRING2, STRING connects to both]. However, Table 1 has a 1 to many relationship with Table 2 through STRING2.
I am thinking that Table 3's STRING is erroneously linked to Table 2's STRING2, but I am unsure if this is intended, and whether if these types of relationships do exist. I am thinking that STRING could be a composite of STRING and STRING2 in table 2, but I am sure that breaks a few db rules (e.g.atomicity), does anyone have any pointers I should read up on?


texts: How to get the row count from a table related to other table?

texts:
I am new to mysql and database concepts and I will try to explain my question through these two minimal tables, here is the scenario for which I need to write a query,

Table : Fruits
| FruitId | FruitName |
| -------- | -------------- |
| 1        | Mango          |
| 2        | Apple          |
| 3        | Papaya        |

Table : FruitSubClasses
| FruitSubClassID | FruitSubClass   | FruitID|
| ---------- -----| --------------- |-----------|
| 100             | Alphonso        |1|
| 101             | Irwin           |1|
| 200             | Fuji            |2|
| 201             | Golden Delicious|2|

I want to get data of all such Fruits from Table 1 such that there are no rows for these fruits(no subclasses) in Table 2. From what I searched I think I will need to use Joins and Count but I don't know how to use them in this situation.
I can describe it like "For each FruitID in Fruits, Fetch the FruitID and FruitName from Fruits such that this FruitID is not present in FruitSubClasses" but could not make a working query for this.


texts: SQL Server - Linked Server error - Could not connect to server XYZ because

texts:
Server being used: SQL Server 2012 SP3
I just received a strange error (strange because it worked earlier)

could not connect to server LNK_SRV because sa is not defined as a remote login

Not sure why we are receiving the error.

The sys.servers says is_linked = 0 for LNK_SRV - not sure why.
The following returns results

select top 10 * from LNK_SRV.DBName.dbo.TblName

But the procedures are failing

exec LNK_SRV.DBName.dbo.ProcName

It was working fine before we dropped the linked server and re-added it.
Also, the LNK_SRV is being used as for replication as well.

Note: I am aware sa is bad. It will be taken care soon.


texts: Identity column jump in sql server restart

texts:
I had many research on identity column jump in sql server restart.
I know the reason why this happen as it explained here.

I know the solution how to prevent this using either manually perform CHECKPOINT or –t272 flag (Which should not use).

  Why I am here - I am looking for the solution where there no need of
  human interaction to RESEED all them back to there valid value using manual script

I have come up with one solution for which I am not sure - is it good or not?

My Solution

When restart performed either manually or server crash, trigger below SQL script using powershell or bat file (configure using TaskScheduler trigger on windows logon).

Script

-- RESEED all column IDENTITY to 0
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED, 0)
 else
  Print ''Table does not have an identity value'''

-- RESEED to it's last value
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED)
 else
  Print ''Table does not have an identity value'''

Is this good solution? if no then what should be it? any one have other idea?

NOTE:

This gaps are not creating any issue in my application.


texts:
I'm trying to retrieve data from openstreetmaps reverse geocoder with plv8. I have tried to look at the documentation but cannot find any info on how to get data from an URL which is returned as JSON to then parse it in the function.

Can anyone assist on this?


texts: Install/bootstrap mysql without name-based users

texts:
Like many people, I run mysql with skip-name-resolve (docs), and only ever use IP addresses to connect to avoid DNS issues and overhead. When installing, mysql_install_db accepts a --skip-name-resolve switch (docs), so with those two set you would expect it to not create name-based users, but this is not the case. With both of those set, there are two name-based users created and so I get this in my logs on startup:

140309 18:47:16 [Warning] 'user' entry 'root@hostname.localdomain' ignored in --skip-name-resolve mode.
140309 18:47:16 [Warning] 'user' entry '@hostname.localdomain' ignored in --skip-name-resolve mode.

How do I stop it from creating these users in the first place?

I'm running MySQL 5.1.70 on OpenBSD 5.4 (current release package).


texts:
I've got a MongoDB running on a medium EC2 instance and everything seems fine apart from the fact that I keep getting this error:

Could not connect to a primary node for replica set #<Moped::Cluster:70157124022400 @seeds=[<Moped::Node resolved_address="my_ip:my_port">]>

This seems to happen only when Sidekiq get a big loads of queued jobs, so the question is, is this a scalability issue?

Looking at the EC2 instance however, it rarely goes over 30% of CPU usage or 40% memory used.


texts:
I have a system that inserts several million records over the course of 24 hours.  Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 minutes).  Applications need to access small timespans of data very quickly (under a second).  Partitioning the table by hour seemed like the best thing to do.

I made a sample / test setup to make sure the partitioning would work correctly.  Here's my main table:

create table test_table(
    test_table_id bigserial primary key,
    "timestamp" timestamp without time zone default now(),
    did_pass boolean not null
);
create index timestamp_idx on test_table("timestamp");

And then created a separate schema to hold the partition tables:

create schema test_table_parition;

And created the hourly partition tables.  There's a total of 24 partition tables.

create table test_table_partition.h0(
    check((extract(hour from "timestamp")) >= 0 and (extract(hour from "timestamp")) < 1)
)inherits (test_table);
create index timestamp_h0_idx on test_table_partition.h0("timestamp");

create table test_table_partition.h1(
    check((extract(hour from "timestamp")) >= 1 and (extract(hour from "timestamp")) < 2)
)inherits (test_table);
create index timestamp_h1_idx on test_table_partition.h1("timestamp");
.
.
.
create table test_table_partition.h23(
    check((extract(hour from "timestamp")) >= 23 and (extract(hour from "timestamp")) < 24)
)inherits (test_table);
create index timestamp_h23_idx on test_table_partition.h23("timestamp");

Created the insert trigger

 create or replace function test_table_insert_function()
 returns trigger as $$
 declare
 currentHour smallint;
 begin
 currentHour := extract(hour from now());
 if ( currentHour = 0 ) then insert into test_table_partition.h0 values(NEW.*);
 elseif ( currentHour = 1 ) then insert into test_table_partition.h1 values(NEW.*);
 .
 .
 .
 elseif ( currentHour = 23 ) then insert into test_table_partition.h23 values(NEW.*);
 end if;
 return null;
 end;
 $$
 language  plpgsql;

 create trigger insert_test_table_trigger
 after insert on test_table for each row
 Execute procedure test_table_insert_function();

Inserted some dummy data.  The data showed up in the main table and in the partition table (as expected)

insert into test_table(did_pass)
values(false),(false), (true), (true);

And then got the execution plan for a simple test query.

explain select *
from test_table
where "timestamp" > '2015-02-20 11:00:30.152';

The query execution plan is going after the main table and all 24 of the partition tables.  From my understanding of partitioning, the execution plan should only be going after the partition tables with an hour value greater than or equal to 11.

How can I partition tables based on hour?

UPDATE

I was playing around with it a bit more today, and I discovered that running a query directly against the hour value of the table will produce the expected query plan.

For example:

explain select *
from test_table
where extract(hour from "timestamp") >= 11
and extract(hour from "timestamp") <13;

will deliver the expected execution plan:

"Append  (cost=0.00..50.54 rows=11 width=17)"
"  ->  Seq Scan on test_table  (cost=0.00..4.46 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h11  (cost=0.00..1.48 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h12  (cost=0.00..44.60 rows=9 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"

I think I need to change how the table partitioning is being setup.


texts:
I intend to use psql on Ubuntu.
To authenticate, I used many things in the past:

Simply write password when asking
.pgpass file
PGPASSWORD variable

This was several weeks ago.
I think my attempts were not clean and I kinda messed up my configuration:
I deleted the .pgpass file and the PGPASSWORD variable but I get this error:
psql: error: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

It don't even ask for a password.
The fun fact is that when an admin connect on my computer and try whith :
sudo -u myuser psql ...

It works!
What can I do to clean up everything?


texts:
Is it possible to restore the rollback changes to a database? One of my client has done wrong updates on database so most of the tables have been updated with wrong data.
That database is having with Replication, So can anyone suggest me without or less downtime can i restore the database with last 2days of data. Thanks in advance


texts: Unable to LOAD from XML: The used command is not allowed with this MySQL version

texts:
I'm having difficulty loading records from XML on OSX using the command

load xml local infile 'records.xml'
into table Badges
rows identified by '<row>';

and I keep getting the error:

Error Code: 1148. The used command is not allowed with this MySQL version   0.00017 sec

I've checked SHOW VARIABLES WHERE `Variable_name` = 'local_infile'; and it's ON. I've also set it explicitly with SET GLOBAL local_infile = true;. Anyone know why this is happening? I'm using mysql 5.7.


texts:
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.

Why does this query not return an exception?

select * from table_a where name in (select name from table_b);

From what I understand, Oracle sees this as

select * from table_a where name = name;

But what I don't get is why?


texts: alert when any job in a job category fails

texts:
Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?

I am wondering because I would like to set up an e-mail anytime an SSRS subscription fails - and all of these subscriptions are jobs in the category Report Server.

EDIT - it turns out that when an SSRS subscription fails, the job itself does not fail, so my question will not apply to the SSRS subscription monitoring use.  However I would still like to know for other jobs that we run in our environment


texts: Incorrect behaviour of SQL Server 2016 with memory-optimized tables

texts:
Please have a look at the following SQL query:

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
  source_col INT NULL,
  target_col INT not NULL
  INDEX ix_InMemoryTable NONCLUSTERED (target_col)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

DECLARE
  @t dbo.IN_MEMORY_TABLE_TYPE

INSERT @t
(
  source_col,
  target_col
)
VALUES
  (10, 0),
  (0, 0)

UPDATE r1
SET
  target_col = -1
FROM @t r1
WHERE EXISTS
      (
        SELECT *
        FROM @t r2
        WHERE r2.source_col > 0
      )

SELECT *
FROM @t

GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE

When executing it on SQL Server 2014 (12.0.4100.1 X64) the UPDATE in the query performs as expected and the following valid result is returned:

source_col | target_col
----------------------
10         | -1
0          | -1

However, when executing on SQL Server 2016 (13.0.4001.0 X64) not all the rows get updated and the following is returned:

source_col | target_col
----------------------
10         | -1
0          | 0

This looks like a bug to me, does it look so to you?


texts: What's the term that describes filtering query that makes use of indexes

texts:
I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about that, but I can't find it). It has (AFAIK) two or three syllables.

It basically denotes the type of query that makes use of indexes, and constitues of best practices to use when querying the database.

Some of the best practices are these:

do not search by perform functions or computations on column names
do use LIKE operator with trailing wildcards, not leading
compare column value to fixed value (constant),

Eg.

SELECT column, date FROM table (..)

(..) WHERE column LIKE 'A%' -- OK, because looks up the column
(..) WHERE column LIKE '%A%' -- INCORRECT, expensive
(..) WHERE SUBSTRING(column, 1, 3) = 'abc' -- INCORRECT

Do you know what's the term describing these types of queries?

Thanks.


texts: ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table

texts:
When trying to CREATE POLICY there is the following error:
ERROR: row is too big: size XXX, maximum size 8160
Is there a size limitation on the pg_policies table?
Can that be enlarged or maybe it was bad practice to write so many conditions with joins?
For now, I solved it by changing the query to fewer characters but how can that be avoided?
Example for an almost too long policy.
        CREATE POLICY check_out_insert ON check_out FOR INSERT
            WITH CHECK (
                (user_id = current_setting('app.current_user_id')::bigint AND
                file_version_id = ANY(ARRAY(
                    SELECT file_version.id fv_id FROM file_version
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "file_user" ON ("file".id = "file_user".file_id AND file_user.deleted_at IS NULL)
                        WHERE file_user.user_id = current_setting('app.current_user_id')::bigint)
                )
                OR
                true = ANY(
                    ARRAY(SELECT is_owner
                    FROM "group"
                    WHERE id = current_setting('app.current_user_group_id')::bigint
                    LIMIT 1
                    )
                )
                OR
                file_version_id = ANY(
                    ARRAY(SELECT DISTINCT file_version.id fv_id FROM file_version
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "sub_folder" ON (file.sub_folder_id = "sub_folder".id)
                        INNER JOIN "folder" ON (sub_folder.folder_id = "folder".id)
                        INNER JOIN "folder_group" ON (folder_group.folder_id = "folder".id AND folder_group.group_id = current_setting('app.current_user_group_id')::bigint)
                        INNER JOIN "project_user" ON (folder.project_id = "project_user".project_id AND "project_user".user_id = current_setting('app.current_user_id')::bigint)
                        WHERE folder.type = 'nda'
                    )
                )
            )
        );`);


texts:
I have a table with 8 million of records which has 10 columns and one of the column is 'text' type while other are mainly timestamps or varchar(10). I have all the necessary index created . Table size ~10GB while index ~4GB.  While running a point query my select are performing well but when i select 50% of the rows it takes ages to complete ~30mins . I am not selecting text column . Query plan says atleast one index used.

Any reason why table is not performing ? is it due to text column which stores a blob(generally ~32KB)


texts: SQL Replication Monitor does NOT show publication properties - Transactional Replication

texts:
The setup I have is as follows:

a) Version SQL Server 2014 SP1 CU6.
b) publication is on a
   separate    availability group [AG1] and subscriber is part of
   another AG [AG2].
c) Distribution is on a separate host.
d) subscriber is pointing to    listener of AG2 [AG2LN]
e) publication is repointing to listener of    [AG1LN]

Scenario:

I have failed-over publication AG to secondary node and the subscriber AG to its secondary node.

Problem :
 On the publication host [ AG1 - secondary node - current primary],  Using SSMS replication monitor, I am trying to see the properties of the publication.  I am getting the below error message:

Error message:

  SQL Server Cannot retrieve  the information for publication xxx  on host [ AG1 primary node]

Please can someone let me know if this is a limitation of SSMS and if there is a workaround available.


texts: how to get table wise number of deadlocks in sql server?

texts:
I am experiencing deadlocks in Sql Server 2014.

I am aware that we can get total number of deadlocks using performance counters in Sql Server Ref: Number of Deadlocks/sec from https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-locks-object

I would like to know if there is a way to get deadlock count per table?


texts: How to explicitly convert to fixed-length alphanumeric datatype in 4D

texts:
I'm building an ETL process in SSIS that extracts data from a 4D data source using the manufacturer's own ODBC provider (4D v16 ODBC Driver). When I try to extract any type of "string" column, it comes out as a LOB column, meaning that

'Row by Row' fetch method is enforced because the table has LOB column(s).

My go-to solution would be to CAST the offending LOB columns in the source SQL statement to a fixed-length type, but I can't seem to figure out the correct syntax or data type name in 4D. I think the following error message points to a syntax error from 4D:

SELECT x, y, CAST(z AS varchar(100)) AS z
FROM someTbl

Documentation on the database product is a bit thin, but it would seem that there is no such thing as fixed-length alphanumeric datatypes, but rather just a "string" datatype, which the ODBC provider perhaps translates to LOB.

Is there a way to convert the database column to an alphanumeric datatype that SSIS will see as a fixed-length column?


texts: Updating SQL Server 2016SP2 to latest CU in Cluster...what happens?

texts:
Updating to latest CU in a cluster with a bunch of databases.
Can I do this on the passive node without SQL Server kicking on?  Do I need to worry about that?
I think I can run it on the passive, reboot the passive, then failover and ... at this point I'm unsure what happens with the databases in the instance.
Does it go through and update each individual database?
Run update exe on passive node
Reboot passive node
Failover...?


texts: How to export table definitions and data in mysql workbench

texts:
I am using mysql workbench 6.3 and I can´t find a way to generate a sql file with one table definition and data.

I could find where to export data or table definition but not both.


texts: automatic soft NUMA results in odd number of cores

texts:
I ran sp_blitz on one of our SQL Server 2016 production servers and one of the findings was that there were CPUs with an odd number of cores. The detailed message indicated this is a really bad NUMA configuration.

SQL Server 2016 will use automatic soft NUMA if it detects physical processors with more than 8 cores. Our particular server has two sockets with 10 CPUs per socket.

The startup messages in the SQL Server error log indicate:

  Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

As a result SQL Server created four logical NUMA nodes with 5 cores each.

Is this a performance problem?

We're using MAXDOP = 4.

Is parallelism an issue here?


texts: Is there any relation between BSON storage for mongoDB and MySQL storage?

texts: I am working on an application where I need to make analysis that what will be the storage size of BSON structure if same data is stored which is stored in MySQL. Basically I am comparing MongoDB and MySQL storage. Is there any relation between them? or some formula which gives size of BSON storage?


texts: How would I reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008

texts:
I have a customer that has a table in the database with an autonumber id field. The customer uses that id as a user-friendly identifier to a unique record stored in the database. After some time, my application will remove old records in the database, such that there is usually a sliding window of about 30k records at any given time.

This functionality is fine, and has been working for a few years, but now the id's in the database are up in the hundred millions, and it is harder for the customer to now communicate failure id's to the outside world.

What they want to do, and what I am hoping to find out how to do, is to take all of the records in the current table, and reduce their id to start at 1, and then reseed the identity to start counting where the new id would be.

In math:
new id = current id - min(ID)

And then finally:
new seed = max(id)

Thanks

edit

To just help clarify one thing, it's that records are getting regularly deleted from the database so the start:

1-30k Valid Records

After 5 months:

1-2mil - no records
2mil - 2mil + 30k - Valid records

What the user essentially wants to do is to subtract 2mil from all of the id's (still unique), and pretend that the previous records never existed at all.


texts: Where is Newid(), Rand(), and Sysutcdatetime() new value per query vs. row behavior documented?

texts:
Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of  sysutcdatetime()) no matter how complex (afaik).

However NewId() and Rand() do not follow this behavior and diverge from each other as well and it does not seem to be documented.  How to describe?  Are there other functions?

Union All Example:

select newid() as NewId, rand() as Rand, sysutcdatetime() as Utc
union all
select newid(), rand(), sysutcdatetime()
union all
select newid(), rand(), sysutcdatetime()

Select Field Example:

select top 1000000 newid() as NewId, Rand() as Rand, sysutcdatetime() as Utc
  from sys.all_objects AS s1
  cross join sys.all_objects AS s2

Update Example:

create table #t
(
    NewId uniqueidentifier,
    Rand decimal(18,8)
)

insert into #t
select null, null
 union all
select null, null
 union all
select null, null

update #t
   set NewId = newid(),
       Rand = rand();

select * from #t


texts: Is 'internallength' always useful in custom type definition in Postgres?

texts:
In our database we use a custom type to store 3D locations:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

In many examples I see INTERNALLENGTH used as a parameter with the definition of custom types. Also, the documentation tells me that "[t]he default assumption is that it is variable-length", if this parameter is omitted. Is this also true for types like the one above where the size can be calculated (3*4 bytes + 4byte padding)? So should I add 16 as internal length to the definition?

Also, are there other parameters that could improve the performance of handling many (~10,000,000) entries using this type?


texts: Is this the right and efficient way to deal with MySQL query?

texts:
The table I have is

 id,  b_name,    s_name,      from_time,    to_time
  1   Bus1       Stop1        START         0800
  2   Bus1       Stop2        0810          0811
  3   Bus1       Stop3        0820          0821
  4   Bus1       Stop4        0830          0831
  5   Bus1       Stop5        0840          END
  6   Bus2       Stop1        START         0810
  7   Bus2       Stop2        0820          0821
  8   Bus2       Stop3        0830          0831
  9   Bus2       Stop4        0840          0841
 10   Bus2       Stop5        0850          END
 11   Bus3       Stop1        START         0820
 12   Bus3       Stop2        0830          0831
 13   Bus3       Stop3        0840          0841
 14   Bus3       Stop4        0850          0851
 15   Bus3       Stop5        0900          END

I want to display results when searched from stopX to stopY without/within times (from and to times, with these being optional). This is what I did

SELECT     st1.id,
           st1.b_name,
           st1.s_name,
           st1.t_time,
           st2.id,
           st2.b_name,
           st2.s_name,
           st2.f_time
FROM table st1
INNER JOIN table st2
ON         (st1.b_name = st2.b_name)
WHERE      st1.`s_name` = 'Stop2'
AND        st2.`s_name` = 'Stop4'
AND        st1.to_time < st2.from_time
AND        st1.to_time > '0500'
AND        st2.from_time < '0845'

Is this an efficient way to put min load?

Now how do I:

Make the time fields optional?
Stop searching from and to same s_name (like Stop3 to Stop3)?

And as the b_name and s_name fields are being repeated should I put them into a separate table as each bus may have 10-30 stops and a few hundred trips (which may increase)?


texts: Constraint to one of the primary keys as foreign key

texts:
Table1: grid_col (col_id,f_id,f_value)
    (col_id,f_id) is primary key.

Table2: grid (grid_id,col_id,text)
    (grid_id) is primary key.

I want to have a constraint for grid that, col_id should be present in grid_col. I can't have foriegn key constraint here.
I can create a function constraint which scans the grid_col while inserting in grid but in that case it increases the chances of having deadlock.
How to add a constriant here?


texts: Not able to attach database file without transaction log file

texts:
Possible Duplicate:
  How do I attach a database in SQL Server?

I lost the transaction log file. I tried re-attaching the data file without the log file, to create a new log file, SQL Server is giving error:


texts: Could a query be still active after pgAdmin4 crashed?

texts:
As i am asking in the title, I am wondering if a query can be still active after pgAdmin4 crashed ?

Indeed I was running a very long-time query..

CREATE TABLE intersection as (
SELECT buff.rownum as buffrownum,
       ST_Intersection(union.st_union, buff.st_buffer) AS geom
FROM union, buff
WHERE ST_Intersects (union.st_union, buff.st_buffer));

..when pgAdmin crashed... (due to my mistake I believe, as I runned a query in parallel to create a big table setting many rows...)

I have seen this post but as I am a very new user of PostgreSQL/postGIS, I didn't really understood the answer...

I opened again pgAdmin4 and did the query..

SELECT * FROM pg_stat_activity;

..and it returns me :

As you can see the query in question (CREATE...) on the second line has still an "active" state. Does it mean that the query is still active even after pgAdmin4 closed ? I mean will the table "intersection" be created ?


texts: How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

texts:
How can I convert the final integer of a bitwise OR back to it's original set of integers that the bitwise OR operation was applied to?

For example if I have the following set of bit values: {0, 1, 2, 4, 8, 16} and if I have the bitwise OR generated value of 11 then I would like to convert that value of 11 back to 1, 2, and 8 (the only possible combinations of values).


texts: Large Data Long Query A Lot of Joins horibble performance

texts:
I have 3 Tables Person, Family, City

Person Table (6 Million Rows)

id (primary key auto_increment unsigned)
family_id (index)
city_id (index)
full_name
first_name (index)
last_name (index)
date_of_birth
record_number
page_number

Family Table (100k Rows)

id (primary key auto_increment unsigned)
area
street
house

City Table (120 Rows)

id (primary key auto_increment unsigned)
city_name

The Query

SELECT SQL_CALC_FOUND_ROWS
a.id,a.full_name,a.date_of_birth,a.record_number,a.page_number,
family.area,family.street,family.house,
family.family_number,city.city_name
FROM person a
INNER JOIN family ON a.family_id = family.id
INNER JOIN city ON a.city_id = city.id AND a.first_name like '%term%'
JOIN person c ON a.family_id = c.id
JOIN person d ON a.family_id = d.id
JOIN person e ON a.family_id = e.id
AND c.first_name like '%term%'
AND d.first_name like '%term%' AND e.first_name like '%term%' LIMIT 0, 30

This query is taking about 14 seconds! on a Core i7 PC with 8GB RAM
is there any way to improve it ?

this is the EXPLAIN for the query

Is there something wrong with the query ? can i improve it
I added indexes on all rows in the JOIN Clause


texts: oracle active sessions waiting for scheduler

texts:
Load on my 11g RAC looks like this:

and I don't understand how to get insight into what this "scheduler" wait class is.  It seems like "scheduler" that it refers to is the process that runs scheduled maintenance jobs?  But can I see which jobs they are?  All I see is normal queries waiting for the "scheduler".

The wait even class is "resmgr: cpu quantum"


texts:
I am running Postgres 10 in the following configuration:

Centos 7
VM on VirtualBox hypervisor
32 GB RAM
4 CPU
800GB disk space on a compressed zfs volume.

I am trying to load and then query 5.5 billion records on a virtual machine. I was able to load all the data that has 20 columns (varchar(200)) without an index. select * from table queries took 45 minutes or more.

So I attempted to create an index on 1 column in the where clause. The index never completed before the server became unresponsive. I then added in an index and started loading 1 days files at a time. After loading two days with an index a query takes at least 6 minutes (around 25 million rows). I assume this is going to grow as the table grows.

Is the VM properties sufficient or can this be tuned or loaded differently. I considered partitioning tables but all the disks are 7200 RPM and shared across all VMs. I am NOT able to add any more resources as I am at the limit already.

// these are some of the changes I have played with in many variations
shared_buffers = 4GB
huge_pages = try
maintenance_work_mem = 16MB
full_page_writes = off
vm.swappiness=0


texts: MySQL 5.6 "temporal columns" upgrade - datetime etc.

texts:
I upgraded from MySQL 5.1 to 5.6.17. Now in Wordpress plugins some of my timestamp columns have started behaving strangely.

MySQL documentation on this very critical feature enhancement (decimals in timestamp fields) is sloppy. SO one relies on blogs/websites.

Some websites such as this recommend ALTER TABLE...FORCE.

But this did nothing for the older data. Some tables which rely on the uniqueness of the timestamp field, now have ".0000" in the 4 digit suffix. Even the ALTER TABLE FORCE didn't change that data.

Any ideas on what we can do instead of manually tinkering with the timestamp fields? Thanks!


texts: MySQL update based on main table data with cross table subquery

texts:
I have the following SELECT statement which I have simplified here. It actually takes 0.6s to run as it does some calculations based on two tables (table1 & table2).

SELECT
    table1.id,table1.lat,table1.lon,
    (SELECT id
       FROM table2
      WHERE latitude=table1.lat AND longitude=table1.lon
      LIMIT 0,1) AS subqueryresult
FROM
    table1

Maybe this thread will even help me to improve my select. The two tables itself have no common ids.

What I intend to do is update every row of table1 with the value received in subqueryresult. subqueryresult itself results out of a SELECT based on the main table data (table1.lat & table1.lon) and just uses table2 as a reference.

Any idea how to achieve that?