311
Add or edit the following line in your postgresql.conf :
listen_addresses = '*'
Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
Restart Postgresql after adding this with service postgresql restart or ...
29
This solution works for IPv4 / IPv6
nano /var/lib/pgsql/data/pg_hba.conf
add at final
host all all ::1/128 md5
host all postgres 127.0.0.1/32 md5
and then restart postgresql service
/etc/init.d/postgresql restart
24
The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.
Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:
SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND ...
18
The way I solved this was:
Added the line as below in pg_hba.conf:
hostnossl all all 0.0.0.0/0 trust
and this was modified in postgresql.conf, as shown:
listen_addresses = '*'
I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won't want to connect ...
15
Use an array to represent the series of values:
pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])");
$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));
The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.
13
The solution to your problem is a MySQL capability called "partitioning". The documentation is here.
What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime -- assuming that it is known when a record is ...
13
(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):
Lots more features.
CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
...
11
The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.
CREATE TABLE dbo.EmpLeave
(
EmployeeID int,
leave_date date,
leave_type_color char(6)
);
INSERT ...
9
Fresh Postgres 9.5 install, Ubuntu.
The key was the local connection type, since psql uses domain socket connection.
pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all md5
host all all 0.0.0.0/0 md5
8
To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.
Most systems I've seen store either the that the student is present (Positive ...
8
I had the same error, trying to debug locally some application that connects to remote DBs.
When using php < 5.3, everything worked.
When using php 5.3 or greater, error shows up.
After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user.
All databases i use have new password length (41).
I changed the ...
7
This looks like you might have hit a bug logged against Percona Server 5.5:
Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables.
There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment.
The pattern described is:
INSERT a value into a column with a unique constraint....
7
Instructions for Debian users.
Login as posgres user:
$ sudo su - postgres
Get the location of pg_hba.conf by quering the database:
$ psql -c "SHOW hba_file;"
hba_file
-------------------------------------
/etc/postgresql/11/main/pg_hba.conf
(1 row)
Open pg_hba.conf:
nano /etc/postgresql/11/main/pg_hba.conf
Add ...
6
In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:
GRANT ALL ON SCHEMA my_schema TO my_user;
Or at least:
GRANT USAGE ON SCHEMA my_schema TO my_user;
6
I don't use phpPgAmin.
To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column".
A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence.
When you define a column as serial Postgres automatically ...
answered Feb 10 '14 at 7:27
a_horse_with_no_name
65.9k12 gold badges127 silver badges166 bronze badges
6
Add the following line in the bottom of pg_hba.conf:
hostnossl all all 0.0.0.0/0 md5
Add/modify the line in postgresql.conf:
listen_addresses = '*'
MAKE SURE THAT the user that is connecting has a password: (Example connect user named postgres)
a. Run the following psql command with the postgres user account:
sudo -u postgres ...
6
The primary solution is to add parentheses:
( -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
, dt2.name AS name2, dt2.time AS time2
FROM departure_times dt1
JOIN departure_times dt2 USING (tram_id)
WHERE dt1.name = '$name'
AND dt2.name = 'CitySquare'
-- no ORDER BY?
LIMIT 5
)
UNION ALL -- see below
(
SELECT dt1.name, ...
6
The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.
I would perform all this testing on a test server and not in production.
https://...
6
The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of rework you need to do in your code.
Having said that, in the real world requirements change. It's a good idea to invest some time up front to get your data ...
6
When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.
6
Add new category :
$t_cat = 'jeans';//new category to insert
$so_to_set = 2;// so for new category
First update all so greater than or equal to $so_to_set
$query = "update categaries set so = so+1 where so >= $so_to_set";
Now insert new category with the $so_to_set
$query = "insert into categaries(name,so) values($t_cat,$so_to_set)";
...
6
You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.
Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...
6
Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.
Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".
Using parameterized queries ...
5
Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated
Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute
Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ...
If you feel intimidated by SQL, an ORM can help ...
5
Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.
The following query may help you understand this better:
SELECT p.customer_id
, c.name
, sum(t.points)
FROM products p
INNER JOIN types t ON p.type_id = t.id
INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
, c....
5
FULLTEXT needs space to be space, not a "letter". That is, you cannot have spaces in a "word".
I would write application code to ignore 'short' words (such as 'g') and end up with
WHERE MATCH(alternate_name, product_desc, keywords)
AGAINST ('+moto +plus'; IN BOOLEAN MODE)
AND ( alternate_name LIKE '%moto g plus%'
OR product_desc LIKE '%...
5
You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:
Select content, image, imageHeight, imageWidth, link, linkDescription
From news
Where appearanceDate < NOW()
And termDate > NOW()
Order By appearanceDate Desc;
5
MySQL 8
MySQL 8 provides window functions...
SELECT item_id
FROM (
SELECT
item_id,
date,
count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
SELECT
item_id,
date,
date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
FROM foo
) AS t
) AS t2
GROUP BY item_id
HAVING ...
5
select
*
from
t
order by
case
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted before 2*/
, col /*finally sort strings correctly*/
5
Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query?
Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements.
Is it a PHP ...
Only top voted, non community-wiki answers of a minimum length are eligible