x-yuri's questions

我正在尝试将数据从 csv 文件导入 pg 表。我得到的错误是：

$ psql -U postgres -c "COPY users (first_name) FROM '/users.csv' (FORMAT csv)"
ERROR:  attribute 51 of type users has wrong type
DETAIL:  Table has type integer, but query expects character varying.
CONTEXT:  COPY users, line 1

我怎么知道它是关于哪个属性的？就此而言， INSERT 也失败了：

$ psql -U postgres -c "INSERT INTO users (first_name) VALUES ('first_name')"
ERROR:  attribute 51 of type record has wrong type
DETAIL:  Table has type integer, but query expects character varying.

我试图根据\d users输出和attnum值进行计数：

SELECT a.*
FROM pg_attribute a
    JOIN pg_class c on a.attrelid = c.oid
    JOIN pg_namespace n on c.relnamespace = n.oid
WHERE n.nspname = 'public'
    AND c.relname = 'users'
    AND attnum >= 1
ORDER BY attnum

我正在运行 PostgreSQL 12.7。

迁移到其他服务器时是否应该复制此表中的数据？

角色app可以访问数据库app。但是角色app2不能。我没有看到 roleapp的任何特定特权，并且根据以下内容，它不是所有者\l：

app2=> \l

      Name       |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------------+----------+----------+-------------+-------------+--------------------------
 app             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres            +
                 |          |          |             |             | postgres=CTc/postgres   +
                 |          |          |             |             | app=CTc/postgres        +
                 |          |          |             |             | app2=CTc/postgres
...

app2=> \dp
                                               Access privileges
 Schema |                    Name                     |   Type   | Access privileges | Column access privileges
--------+---------------------------------------------+----------+-------------------+--------------------------
 public | access_users                                | table    |                   |
 public | access_users_id_seq                         | sequence |                   |
...


app2=> select * from users;
ERROR:  permission denied for relation users

为什么角色app能够访问数据库？我可以检查什么？

据我所知，它必须是utf8mb4_unicode_ci，但是：

> create table t1 (f1 varchar(255) collate utf8mb4_unicode_ci)
> insert into t1 (f1) values ('?')

我得到的是：

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

我要插入数据库的符号是这个。

在mysql的一般日志中，我看到如下内容：

141017  8:57:31     4 Connect   root@localhost as anonymous on

这个“匿名”是什么意思？我以root@localhost. 我仍然是匿名的，甚至是“匿名”吗？

UPD

$ systemctl stop mysqld
$ rm -f /var/lib/mysql/yuri.log
// enable general log in /etc/mysql/my.cnf
$ systemctl start mysqld
$ mysql -u root -p -BNe 'SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin;'
Enter password:
root@localhost  root@localhost
$ mysql -u root -p -BNe 'SELECT host, user FROM mysql.user;'
Enter password: 
127.0.0.1   root
::1 root
localhost   hostname
localhost   root
$ cat /var/lib/mysql/yuri.log
/usr/bin/mysqld, Version: 10.0.14-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /run/mysqld/mysqld.sock
Time                 Id Command    Argument
141204 19:03:19     3 Connect   UNKNOWN_USER@localhost as anonymous on 
            3 Connect   Access denied for user 'UNKNOWN_USER'@'localhost' (using password: NO)
141204 19:04:55     4 Connect   root@localhost as anonymous on 
            4 Query select @@version_comment limit 1
            4 Query SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin
            4 Quit  
141204 19:05:47     5 Connect   root@localhost as anonymous on 
            5 Query select @@version_comment limit 1
            5 Query SELECT host, user FROM mysql.user
            5 Quit